row wise concatenate with dynamic column in sql?

Question

I need to concatenate row wise columns. I have done it but in my table columns are static, but in my requirement columns will be dynamic.

CREATE TABLE #TEMP
(
ID INT 
,Name VARCHAR (100)
,SNO INT 
,SNO1 INT
,SNO2 INT
)

INSERT INTO #TEMP (ID ,Name,SNO,SNO1,SNO2)
VALUES (1,'SAS',1,2,4),(2,'RAR',5,2,5),(3,'SJS',6,8,6)

SELECT * FROM #TEMP

ID  Name    SNO SNO1 SNO2
1   SAS      1   2     4
2   RAR      5   2     5
3   SJS      6   8     6

SELECT ID,NAME,SNO,(SNO+SNO1)AS SNO1,(SNO+SNO1+SNO2)AS SNO2 FROM #TEMP

ID  NAME    SNO   SNO1     SNO2
1   SAS     1       3         7
2   RAR     5       7         12
3   SJS     6       14        20

So use dynamic sql to do the same thing you are doing with static columns. — Tab Alleman
– Tab Alleman, Commented Jun 30, 2015 at 14:20

Felix Pamittan · Accepted Answer · 2015-06-30 14:49:40Z

You can use Dynamic SQL.

SQL Fiddle

DECLARE @colNames AS VARCHAR(MAX) = ''
DECLARE @sql AS VARCHAR(MAX) = ''

--Generate the dynamic column names
;WITH CteColumns AS(
    SELECT
        c.name,
        rn = ROW_NUMBER() OVER(ORDER BY c.name)
    FROM sys.columns c
    WHERE 
        c.object_id = object_id('Temp')
        AND c.name LIKE 'SNO%' 
)
SELECT
    @colNames = STUFF((
        SELECT ', ' + colName
        FROM(
            SELECT  
                colName = 
                    ISNULL(
                        (SELECT name + ' + '
                        FROM CteColumns t
                        WHERE t.rn < c1.rn
                        ORDER BY name
                        FOR XML PATH(''))
                    , '') + '' + name + ' AS ' + QUOTENAME(name)
            FROM CteColumns c1
        )t
        FOR XML PATH('')
    ), 1, 2, '')

SELECT @sql = 'SELECT ID, NAME, ' + @colNames + ' FROM Temp'

PRINT @sql
EXEC (@sql)

RESULT

| ID | NAME | SNO | SNO1 | SNO2 |
|----|------|-----|------|------|
|  1 |  SAS |   1 |    3 |    7 |
|  2 |  RAR |   5 |    7 |   12 |
|  3 |  SJS |   6 |   14 |   20 |

Collectives™ on Stack Overflow

row wise concatenate with dynamic column in sql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related