May 14, 2014 at 6:28 pm
create table #temp1
(
col1 float,
col2 float,
col3 float,
clo4 float)
insert into #temp1 values (1.5, 1.6,1.7,1.8)
insert into #temp1 values (1.9, 1.0,1.2,1.8)
o/p should display as below is there a way we can do this with cte or some other option
col1 1.5 1.9
col2 1.6 1.0
col3 1.7 1.2
col4 1.8 1.8
May 14, 2014 at 10:36 pm
This query uses UNPIVOT in a CTE. A ROW_NUMBER function assigns a number for each header/value combination, which is then used to set the output column in the latter part of the code.
😎
USE tempdb;
GO
create table #temp1
(
col1 float,
col2 float,
col3 float,
col4 float)
insert into #temp1 values (1.5, 1.6,1.7,1.8)
insert into #temp1 values (1.9, 1.0,1.2,1.8)
/* CTE with a UNPIVOT */
;WITH COL_VAL AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY COLS
ORDER BY (SELECT NULL)
) AS COL_RID
,COLS
,VALUE
FROM
(
SELECT
T.col1,T.col2,T.col3,T.col4
FROM #temp1 T
) AS XT
UNPIVOT
(VALUE FOR COLS IN (col1,col2,col3,col4)) AS SLOC
)
/* Use the row number to assign the
destination column
*/
SELECT
CV.COLS
,CV.VALUE
,CW.VALUE
FROM COL_VAL CV
CROSS APPLY COL_VAL CW
WHERE CV.COLS = CW.COLS
AND CV.COL_RID + 1 = CW.COL_RID
/* CLEAN UP */
DROP TABLE #temp1
Results
COLS VALUE VALUE
----- ------ ------
col1 1.5 1.9
col2 1 1.6
col3 1.7 1.2
col4 1.8 1.8
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply