I have data like this
CREATE TABLE [dbo].[tbl_test](
[status] [int] NOT NULL,
[user_id] [int] NOT NULL,
[value] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[tbl_test] ([status],[user_id],[value]) VALUES (1,1,11)
INSERT INTO [dbo].[tbl_test] ([status],[user_id],[value]) VALUES (2,1,1)
INSERT INTO [dbo].[tbl_test] ([status],[user_id],[value]) VALUES (10,1,89)
INSERT INTO [dbo].[tbl_test] ([status],[user_id],[value]) VALUES (4,1,56)
INSERT INTO [dbo].[tbl_test] ([status],[user_id],[value]) VALUES (3,2,156)
INSERT INTO [dbo].[tbl_test] ([status],[user_id],[value]) VALUES (12,2,75)
status , user_id , value
----------- , ----------- , -----------
1 , 1 11
2 , 1 , 1
10 , 1 , 89
4 , 1 , 56
3 , 2 , 156
12 , 2 , 75
I want to pivot the data on [status] and there are potentially 12 different status levels, even though this query only returns 6 of them. Other queries may return 4, 9 or all 12.
SELECT *
FROM
(
SELECT [status]
,[user_id]
,[value]
FROM [dbo].[tbl_test]
)AS source
PIVOT
(
MAX([value])
FOR [status] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as pvt
I wrote this pivot query to account for the possibility that maybe the query will return all 12 for one or more user_id values. As it runs now columns 1 through 9 and 11 have nulls for all user_ids. How can I write the query to leave out the all null columns?
Greg