select data horizontally

[scottcabral1979]

HI,

I have the following table:

Policy Sequence LossDate Description Amt
123 1 9-1-2012 property 100
123 2 10-1-2012 property 200
345 1 9-1-2012 property 100
345 2 10-1-2012 property 100
678 1 9-1-2012 property 100

I want to select the data as follows:

Policy LossDate1 Description1 Amt1 LossDate2 Description2 Amt2
123 9-1-2012 property 100 10-1-2012 property 200
345 9-1-2012 property 100 10-1-2012 property 100
678 9-1-2012 property 100 NULL NULL NULL

Is there a way to do this using T-SQL?

The max sequence number can vary, so far the max has been 12, but may be higher in the future.

thanks

 

[simian336]

This question comes up often. It can be done but it is generally not recommended. Because you want it fielded and the number of fields continues to grow.

A somewhat better solution is to concat the fields into a varchar(max) field. But then you have spacing issues etc. if you are reporting on it (and you have several differnet fields).

If you still want to do it, one stratagy would be to count the max number of fields you need, create a table with that many fields, then update the table.

Perhaps if you state your goal we could offer other suggestions.

Simi

 

[gmmastros]

I assume you want this because there is a report you are trying to build where you want to show the data this way. If this is the case, I would encourage you to NOT pivot this data using TSQL, but instead, pivot this in whatever application you are using for your reports. The biggest problem here is with the last statement "The max sequence number can vary". This means your query should return a different number of columns based on the data. This is usually a bad idea because it leads to code that is harder to maintain, and harder to use.

One way to accomplish this is:

Select Policy,
       Min(Case When Sequence = 1 Then LossDate End) As LossDate1,
       Min(Case When Sequence = 1 Then Description End) As Description1,
       Min(Case When Sequence = 1 Then Amt End) As Amt1,

       Min(Case When Sequence = 2 Then LossDate End) As LossDate2,
       Min(Case When Sequence = 2 Then Description End) As Description2,
       Min(Case When Sequence = 2 Then Amt End) As Amt2,

       Min(Case When Sequence = 3 Then LossDate End) As LossDate3,
       Min(Case When Sequence = 3 Then Description End) As Description3,
       Min(Case When Sequence = 3 Then Amt End) As Amt3
From   YourTableName
Group By Policy

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

George's code one step further...

Declare @sql varchar(max)
declare @cnt int
declare @max int

select @max=max(Sequence) from #test
set @sql='Select Policy,'
set @cnt=1

While @cnt<=@max
begin
set @sql=@sql + ' Min(Case When Sequence = ' + ltrim(str(@cnt))
set @sql=@sql + ' Then LossDate End) As LossDate1,'

set @sql=@sql + ' Min(Case When Sequence = ' + ltrim(str(@cnt))
set @sql=@sql + ' Then Description End) As Description1,'

set @sql=@sql + ' Min(Case When Sequence = ' + ltrim(str(@cnt))
set @sql=@sql + ' Then Amt End) As Amt1'

print @cnt

if @cnt<@max
set @sql=@sql + ','

set @cnt=@cnt + 1
end
set @sql=@sql + ' From #test Group By Policy;'
print @sql

exec (@sql)


Simi

 

[scottcabral1979]

Thanks Simi,

This code is exactly what I was looking for to build the table dynamically based on the max number of sequence numbers.

I had to update the column names to use the @cnt variable instead of hardcoding the '1'. There was also an extra column at the end before the from statement, so I just added a quick line to get rid of that comma. Other than that, the code worked fine.

thanks again
Scott