SQL Server SQL stacking columns

np8igboo  于 2023-03-07  发布在  其他
关注(0)|答案(3)|浏览(146)

I'm looking to stack two columns into one. I know this can be done with a Union. But the bit I'm struggling with is I need to still identify which column the figure originated. Example below:

from:
| A | B | C |
| ------------ | ------------ | ------------ |
| X | 1 | 2 |
| Y | 3 | 4 |
| Z | 5 | 6 |

to:

Anew
X_B1
Y_B3
Z_B5
X_C2
Y_C4
Z_C6
km0tfn4u

km0tfn4u1#

This is a quite strange requirement. I would suggest this type of odd manipulation is probably better left to the front end.

But the query is pretty straight forward. This will produce the output you are looking for.

declare @Something table
(
    A char(1)
    , B int
    , C int
)
insert @Something values
('X', 1, 2)
, ('Y', 3, 4)
, ('Z', 5, 6)

select CONCAT(A, '_B')
    , B
from @Something s

union all

select CONCAT(A, '_C')
    , C
from @Something s
4ioopgfo

4ioopgfo2#

Something like this mayhaps:

;WITH cte AS (
    SELECT  *
    FROM    (VALUES
            (N'X', 1, 2)
        ,   (N'Y', 3, 4)
        ,   (N'Z', 5, 6)
    ) t (A,B,C)
)
SELECT  CONCAT(c.A, '_', postfix) AS A, value AS new
FROM    CTE c
CROSS APPLY
    (VALUES
        (B, 'B'),
        (C, 'C')
    ) v (value, postfix)
ORDER BY
    postfix,
    A;

With help of CROSS APPLY (VALUES , you explode your rows into two rows, one for B and one for C

gtlvzcf8

gtlvzcf83#

You can use the UNPIVOT() function for this, e.g.

DECLARE @T TABLE (A CHAR(1), B INT, C INT);
INSERT @T (A, B, C)
VALUES ('X', 1, 2), ('Y', 3, 4), ('Z', 5, 6);

SELECT  A = CONCAT(upvt.A, '_', upvt.X), upvt.New
FROM    @T AS t
        UNPIVOT (New FOR X IN (B, C)) AS upvt
ORDER BY upvt.X, upvt.A;

相关问题