Convert column name & value into rows name & row value in sql server 2008

Question

Please find following screenshot to understand exact problem. I have checked all solution related converting column into row, but my problem is little bit different. Here I want to convert column name and column value into row value and making column value to row name. I really appreciate detailed explanation of solution.

Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — veritaS, Jan 25 '19 at 07:24
`UNPIVOT` can be helpful here, see [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017). — Tim Biegeleisen, Jan 25 '19 at 07:24

score 1 · Answer 1 · answered Jan 25 '19 at 07:23

You can try to use condition aggravate function with UNION ALL

SELECT TYear,
       TMonth,
       'D1' Day,
       MAX(CASE WHEN USER = 'Sam' THEN D1 END) Sam,
       MAX(CASE WHEN USER = 'Rahul' THEN D1 END) Rahul
FROM tblCallAttend
GROUP BY TYear,
         TMonth
UNION ALL
SELECT TYear,
       TMonth,
       'D2' Day,
       MAX(CASE WHEN USER = 'Sam' THEN D2 END) Sam,
       MAX(CASE WHEN USER = 'Rahul' THEN D2 END) Rahul
FROM tblCallAttend
GROUP BY TYear,
         TMonth
SELECT TYear,
       TMonth,
       'D3' Day,
       MAX(CASE WHEN USER = 'Sam' THEN D3 END) Sam,
       MAX(CASE WHEN USER = 'Rahul' THEN D3 END) Rahul
FROM tblCallAttend
GROUP BY TYear,
         TMonth
.....

Sreenu131 · Answer 2 · 2019-01-25T07:56:42.463

Sample Data

;WITH CTE(Tyear,Tmonth,[User],d1,d2,d3,d4,d5)
AS
(
SELECT 2019,'Jan','Sam',249,297,296,288,269 UNION ALL
SELECT 2019,'Jan','Rahul',300,237,452,142,475
)
SELECT * INTO #Temp FROM CTE

Sql Script

SELECT [Tyear],Tmonth,Dayz,[Sam],[Rahul] 
FROM
(
SELECT o.Tyear,
       o.Tmonth ,
       Dayz,
       dayval,
       dt.[User]
FROM #Temp o
CROSS APPLY ( VALUES ('d1',d1,[User]),('d2',d2,[User]),('d3',d3,[User]),('d4',d4,[User]),('d5',d5,[User])) 
AS dt (Dayz,dayval,[User]) 
)AS SRc
PIVOT
(
MAX(dayval) FOR [User] IN ([Sam],[Rahul])
)AS Pvt

Result

Tyear   Tmonth  Dayz    Sam  Rahul
----------------------------------
2019    Jan     d1      249  300
2019    Jan     d2      297  237
2019    Jan     d3      296  452
2019    Jan     d4      288  142
2019    Jan     d5      269  475

Gordon Linoff · Accepted Answer · 2019-01-25T12:42:43.870

You want to unpivot the data. I recommend a lateral join for this, which in SQL Server means the apply keyword:

select ca.tyear, ca.tmonth, v.day,
       (case when ca.user = 'Sam' then v.val end) as Sam,
       (case when ca.user = 'Rahul' then v.val end) as Rahul
from tblCallAttend ca cross apply
     (values ('D1', ca.d1),
             ('D2', ca.d2),
             ('D3', ca.d3),
             ('D4', ca.d4),
             ('D5', ca.d5)
     ) v(day, val)
order by ca.tyear, ca.tmonth, v.day;

Lateral joins are very powerful. A problem such as unpivoting the data is a convenient way to learn about them.

Convert column name & value into rows name & row value in sql server 2008

3 Answers3