Select numbered column

Question

I have a dynamic select query which supposed to fetches columns as col1, col2......col9, col10 but it fetches incorrectly as col1, col10, col11, col12, col2.... Not sure how to select them as the query is dynamic.

Please help.

This is the dynamic select I have been using.

-- CREATE THE COLUMNS REQUIRED 
SET @DYColumns = STUFF(( SELECT DISTINCT 
    ',' + N'sourceID' 
    + CAST(ROW_NUMBER() 
          OVER (PARTITION BY prgmg_product_id ORDER BY prgmg_product_id, source_id_other) 
         AS NVARCHAR(10)) 
    FROM #Prgmg FOR XML PATH('') ), 1, 1, '');

The code snippet is easier to debug than your paragraphed question. — faheem farhan, Jun 28 '16 at 18:16

score 0 · Answer 1 · answered Jun 28 '16 at 18:15

0

If you are using COALESCE or STUFF to build your columns, make sure there is an ORDER by

Furthermore, you could have col01,col02,..col10 to ensure the sequence

answered Jun 28 '16 at 18:15

John Cappelletti

79,615
7
44
66

How to select dynamically as col01,col02,col03..then – Ramaswamy Jun 28 '16 at 18:26
There are many techniques on building Dynamic SQL. I would have to see your technique to better advice. – John Cappelletti Jun 28 '16 at 18:27
-- CREATE THE COLUMNS REQUIRED SET @DYColumns = STUFF(( SELECT DISTINCT ',' + N'sourceID' + CAST(ROW_NUMBER() OVER (PARTITION BY prgmg_product_id ORDER BY prgmg_product_id, source_id_other) AS NVARCHAR(10)) FROM #Prgmg FOR XML PATH('') ), 1, 1, ''); This is one am using... – Ramaswamy Jun 28 '16 at 18:32
--CREATE THE COLUMNS REQUIRED SET @DYColumns = STUFF(( SELECT DISTINCT ',' + N'sourceID' + right('00'+CAST(ROW_NUMBER() OVER (PARTITION BY prgmg_product_id ORDER BY prgmg_product_id, source_id_other) AS NVARCHAR(10)),2) FROM #Prgmg FOR XML PATH('') ), 1, 1, ''); – John Cappelletti Jun 28 '16 at 18:39
Added padded zero on the column name -- assuming not more than 99 columns – John Cappelletti Jun 28 '16 at 18:40
Or you can add ORDER BY prgmg_product_id, source_id_other after the FROM #Prgmg – John Cappelletti Jun 28 '16 at 18:42
I am a little concerned over the PARTITION BY prgm_product_id. If you have multiple, the Row_Number will reset and potentially cause two Col01's – John Cappelletti Jun 28 '16 at 18:49
Yes john you can also see my question in the below link http://stackoverflow.com/questions/37595058/sql-server-dynamic-columns-creation where you could see the input also ...is there an alternate way i could try??? – Ramaswamy Jun 28 '16 at 19:01
OK, I got the sql from the linked question. Give me a moment – John Cappelletti Jun 28 '16 at 19:03
See the new answer posted – John Cappelletti Jun 28 '16 at 19:13

score 0 · Accepted Answer · answered Jun 28 '16 at 19:13

Drop Table #Prgmg
CREATE TABLE #Prgmg (
    prgmg_product_id INT
    ,source_id_other INT
    );

INSERT #Prgmg (
    prgmg_product_id
    ,source_id_other
    )
VALUES (3310,11478)
    ,(3337,10833)
    ,(3354,11466)
    ,(4039,4846)
    ,(4039,65454)
    ,(4039,65456)
    ,(13337,110833)   -- Added to force over 10
    ,(13354,111466)   -- Added to force over 10
    ,(14039,14846)    -- Added to force over 10
    ,(14039,165454);  -- Added to force over 10


DECLARE @DYColumns NVARCHAR(1000)
    ,@DYSqlQuery NVARCHAR(4000);

--  CREATE THE COLUMNS REQUIRED
SET @DYColumns = STUFF((
            SELECT DISTINCT ',' 
                    + N'sourceID' 
                    + right('00'+CAST(ROW_NUMBER() OVER (ORDER BY prgmg_product_id, source_id_other) AS NVARCHAR(10)),2)
            FROM #Prgmg
            FOR XML PATH('')
            ), 1, 1, '');



-- CREATE THE DYNAMIC SQL AND ADD IN THE CREATED COLUMNS
SET @DYSqlQuery = '
    SELECT prgmg_product_id,' 
        +   @DYColumns 
        +   ' FROM (
                SELECT prgmg_product_id
                    ,CAST(N''sourceID'' + CAST(ROW_NUMBER() OVER (
                        PARTITION BY prgmg_product_id ORDER BY prgmg_product_id, source_id_other
                        ) AS NVARCHAR(10)) AS NVARCHAR(100)) AS Col
                    ,source_id_other
                FROM #Prgmg S1
        ) X
        PIVOT(MIN(source_id_other) FOR Col IN (' + @DYColumns + ')) P'

Print @DYSqlQuery
--EXECUTE sp_executesql @DYSqlQuery;

Returns -- Notice the Columns are in Order. This was done by zero padding the Row_Number()

SELECT prgmg_product_id,sourceID01,sourceID02,sourceID03,sourceID04,sourceID05,sourceID06,sourceID07,sourceID08,sourceID09,sourceID10 FROM (
            SELECT prgmg_product_id
                ,CAST(N'sourceID' + CAST(ROW_NUMBER() OVER (
                    PARTITION BY prgmg_product_id ORDER BY prgmg_product_id, source_id_other
                    ) AS NVARCHAR(10)) AS NVARCHAR(100)) AS Col
                ,source_id_other
            FROM #Prgmg S1
    ) X
    PIVOT(MIN(source_id_other) FOR Col IN (sourceID01,sourceID02,sourceID03,sourceID04,sourceID05,sourceID06,sourceID07,sourceID08,sourceID09,sourceID10)) P

Select numbered column

2 Answers2