SQL Transpose rows to columns (group by key variable) NOT using pivot

Question

The following code is close to doing what I need EXCEPT I should generate columns (p1,p2,p3...) dynamically AND I can't use pivot (see SQL Transpose rows to columns (group by key variable)).

What I am using now in SQL Server 2008:

Select InvoiceNum, 
Max(Case when seq =0  Then product_Description end) as p1,
Max(Case when seq =1 Then product_Description end) as p2

 From  

( Select InvoiceNum, Product_Description,

    Row_Number() Over(Partition by InvoiceNum
        Order by InvoiceNum) - 1 seq

From #tmpTable
)d
 Group by InvoiceNum

Thank you StackOverFlow Community!!

You'll need to use dynamic sql. The question you link to includes an example of how to do this. Oh, and you can use PIVOT for this. — Taryn, Apr 16 '15 at 16:22
I get 'Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.' — Khris Kramer, Apr 16 '15 at 16:24
Have you tried writing any dynamic sql like the example in the link? — Taryn, Apr 16 '15 at 16:32
You can do this as a crosstab. I prefer that to PIVOT anyway. It is slightly faster, easier to read imho and works even with a legacy compatibility level. http://www.sqlservercentral.com/articles/T-SQL/63681/ http://www.sqlservercentral.com/articles/Crosstab/65048/ — Sean Lange, Apr 16 '15 at 16:34
What is the current compatibility level of your SQL 2008 server? — Tab Alleman, Apr 16 '15 at 18:06

score 1 · Answer 1 · answered Apr 17 '15 at 07:32

I think, like the comments also say, you should use Dynamic SQL with a PIVOT table. It can probably be done more efficiently than this.

Create the table and put some data in it:

create table invoiceTable (
    InvoiceNum int,
    Product_Description nvarchar(20)
)

insert into invoiceTable values (1, 'inv1 row 1'),(1, 'inv1 row 2'),(1, 'inv1 row 3'),(2, 'inv2 row 1'),(2, 'inv2 row 2')

declare the necessary variables

declare @maxNo int
declare @query nvarchar(max)
declare @i int = 0

find the max row number:

select @maxNo = max(seq) from 
    (
    Select 
         InvoiceNum
        ,Product_Description
        ,Row_Number() Over(Partition by InvoiceNum Order by InvoiceNum) - 1 seq
    From 
        invoiceTable
    ) maxRowNo

Build the dynamic query

set @query = 
'
Select 
    *
From  
( 
    Select 
         InvoiceNum
        ,Product_Description
        ,Row_Number() Over(Partition by InvoiceNum Order by InvoiceNum) - 1 seq
    From 
        invoiceTable
) d
pivot
(
    min(Product_Description)
    for seq in (
    '

while (@i <= @maxNo)
begin
    if (@i > 0) set @Query += ','
    set @query += '['+CAST(@i as nvarchar)+']'

    set @i += 1;
end

set @query +=
    ')
) pvt
'

Execute the query

exec sp_executesql @query

This gives the following resultset:

InvoiceNum  0           1           2
1           inv1 row 1  inv1 row 2  inv1 row 3
2           inv2 row 1  inv2 row 2  NULL

SQL Transpose rows to columns (group by key variable) NOT using pivot

1 Answers1