SQL Server: combining rows

Question

I have problem with combine four rows (value3 = rowNumber) into one row

I have something like that:

id  value1  value2  value3
 A1   11       21      1
 A2   12       22      2
 A3   13       23      3
 A4   14       24      4

And I need something like that

id  value1  value2  id  value1  value2  id value1  value2  id  value1 value2
 A1    11      21    A2     12     22    A3   13      23    A4    14     24

Could any one please help me on this?

Why do you want to do this? This approach will get very ugly very fast as your number of rows increases. Probably better to handle it in your presentation layer. — Tim Biegeleisen, Nov 23 '17 at 09:40
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Patrick, Nov 23 '17 at 09:49

score 0 · Answer 1 · answered Nov 23 '17 at 09:47

Try this,

CREATE TABLE #Table(Id varchar(5), value1 int, Value2 int,value3 int)
INSERT INTO #Table VALUES('A1',11,21,1)
INSERT INTO #Table VALUES('A2',12,22,2)
INSERT INTO #Table VALUES('A3',13,23,3)
INSERT INTO #Table VALUES('A4',14,24,4)


SELECT T1.Id,T1.value1,T1.Value2,T2.Id,T2.value1,T2.Value2,T3.Id,T3.value1,T3.Value2,T4.Id,T4.value1,T4.Value2
FROM #Table T1
INNER JOIN #Table T2 ON T1.value3=T2.value3-1
INNER JOIN #Table T3 ON T1.value3=T3.value3-2
INNER JOIN #Table T4 ON T1.value3=T4.value3-3

Hope this helps you.

Yogesh Sharma · Answer 2 · 2017-11-23T10:48:36.327

This one is very ugly but you could try conditional case aggregation

SELECT MAX(CASE(ID) WHEN 'A1' THEN ID ELSE NULL END) [A1],
       MAX(CASE(ID) WHEN 'A1' THEN value1 ELSE NULL END) [value1],
       MIN(CASE(ID) WHEN 'A1' THEN value2 ELSE NULL END) [value2],
       ..
       ..
       MAX(CASE(ID) WHEN 'A4' THEN ID ELSE NULL END) [A4],
       MAX(CASE(ID) WHEN 'A4' THEN value1 ELSE NULL END) [value1],
       MIN(CASE(ID) WHEN 'A4' THEN value2 ELSE NULL END) [value2]

       FROM <table>

Result :

id  value1  value2  id  value1  value2  id value1  value2  id  value1 value2
 A1    11      21    A2     12     22    A3   13      23    A4    14     24

score 0 · Answer 3 · answered Nov 23 '17 at 10:10

Try this code for your best solution,

                    DECLARE @table TABLE(ID VARCHAR(50),value1 VARCHAR(50),value2 VARCHAR(50),value3 VARCHAR(50))


        INSERT INTO @table ( ID, value1, value2, value3 )
        SELECT 'A1',11,21,1
        UNION
        SELECT 'A2',12,22,2
        UNION
        SELECT 'A3',13,23,3
        UNION
        SELECT 'A4',14,24,4





        SELECT Val, pvtCol INTO #table FROM(
        SELECT *,col+'_'+CONVERT(VARCHAR(50),ROW_NUMBER() OVER(ORDER BY (SELECT 1))) pvtCol   FROM
        @table  tbl
        UNPIVOT 
        ( val FOR col IN (ID,value1,value2))
        unPvt
        )x

        DECLARE @PvtCol NVARCHAR(MAX) = STUFF((SELECT ',[' + pvtCol,']' FROM #table FOR XML PATH('')),1,1,'')
        DECLARE @PvtColDisplay NVARCHAR(MAX) = STUFF((SELECT ',[' + pvtCol,'] AS [' + LEFT(pvtCol,CHARINDEX('_',pvtCol)-1),']' FROM #table FOR XML PATH('')),1,1,'')

        EXEC ('SELECT '+@PvtColDisplay+' FROM #table rdyPvt
        PIVOT 
        (
        MAX(Val) FOR pvtCol IN ('+@PvtCol+')
        ) pvt')



        DROP  TABLE #table

SQL Server: combining rows

3 Answers3