Dynamic SQL: Grouping by one variable, counting another for column names

Question

I am trying to do a dynamic sql query, similar to some that have appeared on this forum, but for the life of me, I cannot get it to work.

I am using SQL Server 2008. I have a table with a series of order_ref numbers. Each of these numbers has a varying number of advice_refs associated with it. advice_ref numbers are unique (they are a key from another table). There is at least one advice_ref for each order_ref. There are a bunch of columns that describe information for each advice_ref.

What I want to do is create a table with a row for each unique order_ref, with columns for each advice_ref, in ascending order. The columns would be Advice01, Advice02, ....Advice10, Advice11, etc. Not all the Advice# columns would be filled in for every order_ref and the number of advice# columns would depend on the order_ref with the greatest number of advice_refs.

The table would look like:

Order Advice01  Advice02  Advice03  Advice04.....
  1       1         2         3        
  2       5         8         9        20
  3       25

The code I've tried to use is:

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @PVT NVARCHAR(MAX)

    SELECT  @SQL = @SQL + ', COALESCE(' + QUOTENAME('Advice' + RowNum) + ', '''') AS ' +  QUOTENAME('Advice' + RowNum),
            @PVT = @PVT + ', ' + QUOTENAME('Advice' + RowNum)
    FROM    (SELECT case when RowNum2 < 10 then '0'+RowNum2 when RowNum2 >=10 then RowNum2 end [RowNum] From
    (   SELECT  DISTINCT CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY order_ref ORDER BY advice_ref)) [RowNum2]
                FROM    [ED_dups].[dbo].[NewEDDupsLongForm]
            ) rn2 ) rn

    SET @SQL = 'SELECT order_ref' + @SQL + '
                FROM    (   SELECT  order_ref, 
                                    advice_ref, 
                                    case when CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY order_ref ORDER BY advice_ref)) < 10 
                                         then ''Advice0'' + CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY order_ref ORDER BY advice_ref))
                                         else ''Advice'' + CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY order_ref ORDER BY advice_ref)) 
                                         end [AdviceID]
                            FROM    [ED_dups].[dbo].[NewEDDupsLongForm]
                        ) data
                        PIVOT
                        (   MAX(advice_ref)
                            FOR AdviceID IN (' + STUFF(@PVT, 1, 2, '') + ')
                        ) pvt'

    EXECUTE SP_EXECUTESQL @SQL

SQL server tells me that the query executed successfully, but there is no output. When I run snippets of the code, it seems that the problem either lies in the pivot statement, near + STUFF(@PVT, 1, 2, '') + ') and/or in the select statement, near ''Advice0'' +

Thanks in advance for any help--I've been at this for days!

Do your values look as you'd expect them to when you PRINT @SQL at each step? — Hart CO
– Hart CO, Commented Sep 24, 2013 at 16:32

roman · Accepted Answer · 2013-09-24 16:40:36Z

2

I think you have to initialize variables like

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @PVT NVARCHAR(MAX) = ''

or

DECLARE @SQL NVARCHAR(MAX)
DECLARE @PVT NVARCHAR(MAX)

SELECT @SQL = '', @PVT = ''

Otherwise your @SQL would be null

answered Sep 24, 2013 at 16:40

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hart CO Over a year ago

Good catch, I agree. NULL + anything = NULL

roman Over a year ago

@GoatCO I've encountered this problems so many times in the past :)

user2811528 Over a year ago

Woot! That really helped. Now I just need to figure out how to get the columns to be in numerical order.

roman Over a year ago

@user2811528 I think you can change your last string to ) rn2 ) rn order by RowNum

lot · Accepted Answer · 2013-09-24 16:42:49Z

fist thing that comes to my mind is - do you really need SQL to fetch you dataset with dynamic number of columns? If you are writting an application, then your user interface, being it a web page or desktop app form, would be much nicer place to transform your data into a desired structure.

If you really need to do so, you will make your life much easier when you will not try to do everything in one big and rather complicated query, but rather split it into smaller tasks done step by step. What I would do is to use temporary tables to store working results, then use cursors to process order by order and advice by advice while inserting my data into temporary table or tables, in the end return a content of this table. Wrap everything in a stored procedure. This method will also allow you to debug it easier - you can check every single step if it has done what it was expected to do.

And final advice - share a definition of your NewEDDupsLongForm table - someone might write some code to help you out then.

cheers

Collectives™ on Stack Overflow

Dynamic SQL: Grouping by one variable, counting another for column names

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related