Click here to Skip to main content
14,839,738 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I'm trying to convert columns to rows.

This is my query.
SQL
SELECT SUM(CASE WHEN [Domain ID] = 1 THEN [Fix Count] ELSE 0 END) as [test_1],
       SUM(CASE WHEN [Domain ID] = 2 THEN [Fix Count] ELSE 0 END) as [test_2],
       SUM(CASE WHEN [Domain ID] = 3 THEN [Fix Count] ELSE 0 END) as [test_3],
       SUM(CASE WHEN [Domain ID] = 4 THEN [Fix Count] ELSE 0 END) as [test_4],
       SUM(CASE WHEN [Domain ID] = 5 THEN [Fix Count] ELSE 0 END) as [test_5]       
FROM (SELECT lg.domainNameID AS [Domain ID], COUNT(*) AS [Fix Count]
      FROM tbl_ATT_Request r JOIN
           tbl_ATT_Login lg
           ON lg.workdayID = r.workdayID
      WHERE r.requestCategoryID = 1
      GROUP BY lg.domainNameID
	  ) slct

This is the output:

test_1 | test_2 | test_3 | test_4 | test_5
1	   | 1	    | 0	     | 2	  | 1


My Desired output is something like this:
Column 1 | Column2
test_1   |  1
test_2   |  1
test_3   |  0
test_4   |  2
test_5   |  1


What I have tried:

This is what I've tried:

SQL
SELECT SUM(CASE WHEN [Domain ID] = 1 THEN [Fix Count] ELSE 0 END) as [test_1],
       SUM(CASE WHEN [Domain ID] = 2 THEN [Fix Count] ELSE 0 END) as [test_2],
       SUM(CASE WHEN [Domain ID] = 3 THEN [Fix Count] ELSE 0 END) as [test_3],
       SUM(CASE WHEN [Domain ID] = 4 THEN [Fix Count] ELSE 0 END) as [test_4],
       SUM(CASE WHEN [Domain ID] = 5 THEN [Fix Count] ELSE 0 END) as [test_5]       
FROM (SELECT lg.domainNameID AS [Domain ID], COUNT(*) AS [Fix Count]
      FROM tbl_ATT_Request r JOIN
           tbl_ATT_Login lg
           ON lg.workdayID = r.workdayID
      WHERE r.requestCategoryID = 1
      GROUP BY lg.domainNameID
	  ) slct
UNPIVOT(
	COLUMN1
	FOR COLUMN2 IN ([test_1],[test_2],[test_3],[test_4],[test_5])
) unpvt

And this is the error I got:
Msg 207, Level 16, State 1, Line 30
Invalid column name 'test_1'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'test_2'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'test_3'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'test_4'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'test_5'.
Posted
Updated 10-Feb-21 8:12am

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900