SQLTeam.com | Weblogs | Forums

Remove blank columns from pivot


#1

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


#2

If I understand your request correctly, you want to make your columns dynamic, not hard coded. I put this together to help me understand- hopefully it will help you as well.

DECLARE @columns VARCHAR(80)

SELECT @columns = COALESCE(@columns + ',[' + cast(status as varchar) + ']',
'[' + cast(status as varchar)+ ']')
FROM [tbl_test]
GROUP BY status

DECLARE @query VARCHAR(800)

SET @query = '
SELECT *
FROM [tbl_test]
PIVOT
(
MAX([value])
FOR [status] IN (' + @columns + ')
) as pvt'

EXECUTE(@query)

This site (http://www.tsqltutorials.com/pivot.php) helped me grasp the concept, it may help you as well.

Craig


#3

Craig,

Yes, I can see what you're doing. Thanks.

Greg