SQL - query for combining multiple rows

Question

I'm searching but i can't find a solution for my problem. I've a table (say T1) with this data:

SampleCode	Name	Content
#	1	A#
#	2	B#
#	3	C#
@	1	A@
@	2	B@
@	3	C@

So i need a select query resulting in :

Column 1	Column 2	Column 3
SampleCode	#	@
Name.1	A#	A@
Name.2	B#	B@
Name.3	C#	C@

Does anyone has an hint?

Thanks

Fabio

Answer 1

Pivot table can solve your prooblem:

DECLARE @T TABLE (SampleCode sysname,   Name sysname,   Content sysname)

INSERT @T (SampleCode, Name, Content)
VALUES
('#', '1', 'A#'),
('#', '2', 'B#'),
('#', '3', 'C#'),
('@', '1', 'A@'),
('@', '2', 'B@'),
('@', '3', 'C@')

SELECT CONCAT('Name.', PIVOTTABLE.Name), [#], [@]
FROM    
(
SELECT * FROM @T AS t
) AS SOURCE
PIVOT (
MAX(Content)
FOR SampleCode IN ([#], [@])
) AS PIVOTTABLE

Of course this is the solution for what you provided in the question. When the columns are too many to manually write down then use Dynamic query and the only parts needed to be generate is the values in FOR expression and the same value in SELECT

Answer 2

Check out the 2 options in this article: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

If using TSQL, you could use a TRANSPOSE statement. Or you can create the result set using cursors/loops and dynamic SQL.

Fully Dynamic Solution

I can't believe I hammered this out... maybe I did it just to see if I could. It's long and complicated and uses several loops. I think you should re-evaluate what you're trying to do. This can't be run in a view. You would have to put into a function or stored procedure. You may want to evaluate the temp table names to see if they are compatible with your environment. Also, since the temp table name are not unique, you cannot run multiple instances; if you want to run multiple instances, you would have to add a unique identifier to a column in the temp tables or to temp table names. This is fully dynamic... you don't have to know how many columns you need ahead of time.

--**************************************************
--Header area... sample data and temp tables.
--**************************************************
--Clean up sample and result tables if they exist.
IF OBJECT_ID(N'tempdb..#T1') IS NOT NULL  
   DROP TABLE #T1;  

IF OBJECT_ID(N'tempdb..#ResultsTemp') IS NOT NULL  
   DROP TABLE #ResultsTemp;  

IF OBJECT_ID(N'tempdb..#codeTable') IS NOT NULL  
   DROP TABLE #codeTable;  

--For Debugging.
DECLARE @debug int = 1; --0=no debug, 1=show debug messages, do not exec all SQL statements.

--Table var to hold sample data.
CREATE TABLE #T1 (
    SampleCode nvarchar(50),
    [Name] nvarchar(50),
    Content nvarchar(50),
    row_num int
);

--Load Sample Data.
INSERT INTO #T1 (SampleCode, [Name], Content)
VALUES 
    ('#', '1', 'A#')
    , ('#', '2', 'B#')
    , ('#', '3', 'C#')
    , ('@', '1', 'A@')
    , ('@', '2', 'B@')
    , ('@', '3', 'C@')
;

--**********END HEADER**********



--Number the rows so we can loop over them.
UPDATE #T1 
SET row_num = newT1.row_num
FROM  
(
    SELECT t12.SampleCode
        , t12.[Name]
        , T12.Content
        , ROW_NUMBER()OVER(ORDER BY SampleCode, [Name], Content) as row_num
    FROM #T1 as t12
) AS newT1
WHERE #T1.SampleCode = newT1.SampleCode
    AND #T1.[Name] = newT1.[Name]
    AND #T1.Content = newT1.Content
;
SELECT * FROM #T1;  --Debugging... just show the contents of #T1 after adding row_num

--Create temp table to load results.
CREATE TABLE #ResultsTemp (
    Column1 nvarchar(50)
);

--Create some variable to track looping (without a cursor).
DECLARE @loopCount int = 1;
DECLARE @maxLoops int = (SELECT COUNT(DISTINCT SampleCode) FROM #T1);
DECLARE @sql nvarchar(512);

--Add columns to #ResultsTable dynamically
WHILE (@loopCount <= (@maxLoops))
BEGIN
    SET @sql = 'ALTER TABLE #ResultsTemp ADD ' + QUOTENAME('Column' + CAST((@loopCount + 1) as nvarchar)) + ' nvarchar(50) NULL';
    IF (@debug = 1) PRINT @sql;
    EXEC (@sql);

    SET @loopCount = @loopCount + 1;
END

--************************************************************
--SECTION FOR INSERTING FIRST ROW for "SampleCode"
--************************************************************
--Vars for tracking the SampleCode variations processed.
DECLARE @sampleLoop int = 1;
DECLARE @sampleCount int = (SELECT COUNT(DISTINCT SampleCode) FROM #T1);
CREATE TABLE #codeTable (
    SampleCode nvarchar(50)
    , row_num int
);

--Create a list of unique SampleCodes to loop over.
INSERT INTO #codeTable (SampleCode)
SELECT DISTINCT 
    SampleCode 
FROM #T1;

UPDATE #codeTable 
SET row_num = newCT.row_num
FROM  
(
    SELECT ct2.SampleCode
        , ROW_NUMBER()OVER(ORDER BY SampleCode) as row_num
    FROM #codeTable as ct2
) AS newCT
WHERE #codeTable.SampleCode = newCT.SampleCode
;

SET @sql = 'INSERT INTO #ResultsTemp (Column1) SELECT ''SampleCode''';
IF (@debug = 1) PRINT @sql;
EXEC (@sql);

WHILE (@sampleLoop <= @sampleCount)
BEGIN
    SET @sql = 'UPDATE #ResultsTemp SET Column' + CAST(@SampleLoop + 1 as nvarchar) + '=(SELECT TOP 1 SampleCode FROM #codeTable WHERE row_num = ' + CAST(@sampleLoop as nvarchar) + ');';
    IF (@debug = 1) PRINT @sql;
    EXEC (@sql);
    SET @sampleLoop = @sampleLoop + 1;
END

IF (@debug = 1) SELECT * FROM #ResultsTemp;

--**********END SECTION**********


--**************************************************
--SECTION FOR INSERTING THE REST OF THE CONTENT DATA
--**************************************************

--Vars for tracking number of rows processed from T1.
SET @loopCount = 1;
SET @maxLoops = (SELECT MAX(row_num) FROM #T1);

--Loop over each row in T1.
WHILE (@loopCount <= @maxLoops)
BEGIN
    --Create a name for this row.
    DECLARE @nameRaw nvarchar(50) = (SELECT TOP 1 [Name] FROM #T1 WHERE row_num = @loopCount);
    DECLARE @codeNum nvarchar(50) = (
        SELECT TOP 1 ct.row_num 
        FROM #T1 as t
            INNER JOIN #codeTable as ct
                ON ct.SampleCode = t.SampleCode
            WHERE t.row_num = @loopCount);
    DECLARE @name nvarchar(50) = 'Name.' + @nameRaw;

    --First insert a row for this Name... if not already in the list.
    SET @sql = 'INSERT INTO #ResultsTemp (Column1)
        SELECT ''Name.'' + t.[Name]
        FROM #T1 as t
            LEFT OUTER JOIN #ResultsTemp as rt
                ON rt.Column1 = ''' + @name + '''
        WHERE t.row_num = ' + CAST(@loopCount as nvarchar) + '
            AND rt.Column1 IS NULL;';
    IF (@debug = 1) PRINT @sql;
    EXEC (@sql);

    --Update this Name row with the "content".
    SET @sql = 'UPDATE rt 
        SET Column' + CAST(@codeNum + 1 as nvarchar) + '=t.Content
        FROM #ResultsTemp as rt
            INNER JOIN #T1 as t
                ON t.row_num = ' + CAST(@loopCount as nvarchar) + '
                AND t.[Name] = ''' + @nameRaw + '''
        WHERE rt.Column1 = ''' + @name + ''';';
    IF (@debug = 1) PRINT @sql;
    EXEC (@sql);

    SET @loopCount = @loopCount + 1;
END

--Show everything in the temp Results table.
SELECT *
FROM #ResultsTemp;

Result Set:

Static Select Solution

This next option is not dynamic. You have to know how many columns you need and then you can copy the "Column3" code to any new columns. You have to change the column name and update the "Select TOP 1" statement as commented for each new column you would copy.

WITH CodeTable AS (
    SELECT DISTINCT t.SampleCode
    FROM #T1 as t
)
SELECT DISTINCT
    'Name.' + [Name]
    , (
        SELECT TOP 1 Content 
        FROM #T1 as t2
        WHERE t2.SampleCode = (
            SELECT TOP 1 SampleCode 
            FROM CodeTable as ct
            ORDER BY SampleCode
        )
            AND t2.[Name] = t.[Name]
      ) as Column2
    , (
        SELECT TOP 1 Content 
        FROM #T1 as t2
        WHERE t2.SampleCode = (
            SELECT TOP 1 SampleCode 
            FROM CodeTable as ct
            WHERE ct.SampleCode NOT IN (
                SELECT TOP 1 SampleCode --Update the TOP 1 to be TOP [number of previous columns]
                FROM CodeTable as ct2
                ORDER BY ct2.SampleCode
            )
            ORDER BY ct.SampleCode
        )
            AND t2.[Name] = t.[Name]
      ) as Column3
FROM #T1 as t

Static PivotTable Solution

This solution is from the link I posted. Again, you have to know the number of columns you need and it doesn't have generic column names like you specify. But, it is another solution if you reconsider your output.

--Use PivotTable.
SELECT *
FROM
(
    SELECT [Name], Content
    FROM #T1
) AS SourceTable PIVOT(MAX(Content) FOR [Content] IN([A#],
                                                     [A@]
                                                     )) AS PivotTable;