May 5, 2017 at 3:09 pm
I have a table that looks like this:
| COL1 | COL2 | COL3 | COL4 | COL5 | COL6 |
| 1 | 2 | AA | BB | CC | DD |
| 1 | 3 | AA | BB | CC | DD |
| 2 | 1 | AA | BB | CC | DD |
| 2 | 2 | AA | BB | CC | DD |
I am trying to un-pivot based off COL1 and COL2 for COL3,COL4,COL5,COL6, but I am doing something very wrong.
SELECT COL1, COL2, NEW_COLUMN
FROM TABLE
UNPIVOT
(
VALUE FOR COL IN (COL3,COL4,COL5,COL6) AS NEW_COLUMN
)
Can someone please help. I know I can do it with unions, but isn't using the pivot function the best method?
May 5, 2017 at 3:52 pm
SELECT COL1, COL2, VALUE AS [NEW_COLUMN]
FROM TABLE AS p
UNPIVOT (
VALUE FOR COL IN ([COL3],[COL4],[COL5],[COL6])
) AS up
May 5, 2017 at 4:03 pm
DesNorton - Friday, May 5, 2017 3:52 PMThis should work
SELECT COL1, COL2, VALUE AS [NEW_COLUMN]
FROM TABLE AS p
UNPIVOT (
VALUE FOR COL IN ([COL3],[COL4],[COL5],[COL6])
) AS up
one small update to Des' script so that you don't lose data in the unpivot process:SELECT COL1, COL2,
COL as PIVOTCOL, --< here's my update
VALUE AS [NEW_COLUMN]
FROM TABLE AS p
UNPIVOT (
VALUE FOR COL IN ([COL3],[COL4],[COL5],[COL6])
) AS up
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 8, 2017 at 7:31 am
I am getting the error
The type of column "COL4" conflicts with the type of other columns specified in the UNPIVOT list.
When running :
SELECT COL1, COL2,
COL as PIVOTCOL, --< here's my update
VALUE AS [NEW_COLUMN]
FROM TABLE AS p
UNPIVOT (
VALUE FOR COL IN ([COL3],[COL4])
) AS up
May 8, 2017 at 7:40 am
Nevermind, datatypes need to be the same! Any way I can do this without making a temp table?
May 8, 2017 at 9:32 am
Maybe something like this would work.
SELECT COL1, COL2, NEW_COLUMN
FROM TABLE
CROSS APPLY (VALUES('COL3', CAST( COL3 AS varchar(100))),
('COL4', CAST( COL4 AS varchar(100))),
('COL5', CAST( COL5 AS varchar(100))),
('COL6', CAST( COL6 AS varchar(100))))up(COL, NEW_COLUMN);
--Or removing the caption to identify rows.
SELECT COL1, COL2, NEW_COLUMN
FROM TABLE
CROSS APPLY (VALUES(CAST( COL3 AS varchar(100))),
(CAST( COL4 AS varchar(100))),
(CAST( COL5 AS varchar(100))),
(CAST( COL6 AS varchar(100))))up(NEW_COLUMN);
May 8, 2017 at 2:09 pm
Thank you so much for the help. (ALL OF YOU!!!)
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply