Dynamic pivot WITHOUT variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic pivot WITHOUT variables

Author

Topic

scottstown1
Starting Member

15 Posts

Posted - 2012-04-12 : 14:18:11

Hello,

I would like to do a dynamic pivot. But without using any variables. Is this possible?

Note: The number of column titles will always be a max of 4.

Ex input:

ID | columntitle
---------------
222 1
222 2
111 3
222 4

Ex output (where id = 222):

ID | columntitle1 | columntitle2 | columntitle3 | columntitle4
------------------------------------------------------------------
222______1___________2___________4__________null___________

If this is possible, could someone please provide a simple example.

Thank you very much for your help!! :)

Scott

Edit: I should have stated in my original question that I will not know the values of the coumntitle so I cannot hardcode them into the SQL.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-12 : 14:33:56

[code]
declare @scottstown TABLE(id int, columntitle int)
insert into @scottstown
select 222, 1
union
select 222, 2
union
select 111, 3
union
select 222, 4

select * From @scottstown

SELECT id ,
[1], [2], [3], [4]
FROM
(SELECT id, columntitle
FROM @scottstown
where id = 222) AS SourceTable

PIVOT
(
AVG(columntitle)
FOR columntitle IN ([1], [2], [3], [4])
) AS PivotTable;
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

scottstown1
Starting Member

15 Posts

Posted - 2012-04-12 : 14:37:08

Hi yosiasz,

Thank you for the answer, but perhaps my question was not well stated. I do not know what the values of the column will be (as they will be different for each ID). So I cannot hardcode them into the SQL, but I also cannot use variables.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-12 : 14:42:16

Here's a small change to yosiaz's query that should do the trick:

SELECT id , 
[1], [2], [3], [4]
FROM
(SELECT id, columntitle, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY columntitle) rn
    FROM @scottstown
    where id = 222) AS SourceTable
    
PIVOT
(
AVG(columntitle)
FOR rn IN ([1], [2], [3], [4])
) AS PivotTable;

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-12 : 14:43:13

the following is your best resource

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

scottstown1
Starting Member

15 Posts

Posted - 2012-04-12 : 15:08:12

It works!!! Thank you so much I really appreciate your help yosiasz and robvolk!. I have been working on this for weeks.

My final code:

SELECT encounter_id , [1], [2], [3], [4]
FROM
(SELECT encounter_id, diagnosis_code, ROW_NUMBER() OVER (PARTITION BY encounter_id ORDER BY diagnosis_code) rn
FROM diagnosis
WHERE encounter_id = 33579) AS SourceTable

PIVOT
(
MAX(diagnosis_code)
FOR rn IN ([1], [2], [3], [4])
) AS PivotTable;

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-12 : 15:09:05

here is how to use that function by madhivanan, but can't get that fourth column with null value in there


create table dbo.scottstown(id int, columntitle int)
insert into dbo.scottstown
select 222, 1
union
select 222, 2
union
select 111, 3
union
select 222, 4

EXEC dbo.dynamic_pivot
'select id, columntitle From scottstown sc1  where id = 222 ',
'''columntitle'' + cast( ISNULL(columntitle,'''') as varchar(12))',
'AVG(columntitle)'

drop table dbo.scottstown

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources