SQL Server T-SQL Rows to Columns

Question

I am not sure If I'm doing it correctly but my requirement was to create a view display rows into columns using TSQL. Column number is fixed. Rows will never exceed the number of columns.

Limit in Col2 is 3. No limit in Col 1.

Currently my SQL is using OFFSET AND FETCH but it seems its always returning a total of 1 row.

 SELECT Col1, Col2 FROM Table2 WHERE Col1 IN (SELECT Col FROM Table1) ORDER BY Col2 ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

Table 1
A
B
C
D

Table 2 Col 1	Table 2 Col 2
A	A1
A	A2
A	A3
B	B1

Output Col 1	Output Col 1	Output Col 2	Output Col 3
A	A1	A2	A3
B	B1	NULL	NULL
C	NULL	NULL	NULL
D	NULL	NULL	NULL

Thanks you.

Could there be a `A4`, a `B7` or even a `D999`? What's the limit here? — Thom A, Feb 07 '23 at 11:23
Yes, already looking at those two from my research. But Im not sure about the syntax or correct sql statement with joins. — Seigfred Mondez, Feb 07 '23 at 11:34

Thom A · Accepted Answer · 2023-02-07T12:36:52.673

1

I would, personally, use conditional aggregation for this, which would look a little like this:

SELECT T1.[Table 1] AS [Output Col 1],
       MAX(CASE [Table 2 Col 2] WHEN T1.[Table 1] + '1' THEN [Table 2 Col 2] END) AS [Output Col 1],
       MAX(CASE [Table 2 Col 2] WHEN T1.[Table 1] + '2' THEN [Table 2 Col 2] END) AS [Output Col 2],
       MAX(CASE [Table 2 Col 2] WHEN T1.[Table 1] + '3' THEN [Table 2 Col 2] END) AS [Output Col 3]
FROM dbo.YourFirstTable T1
     LEFT JOIN dbo.YourSecondTable T2 ON T1.[Table 1] = T2.[Table 2 Col 1]
GROUP BY T1.[Table 1];

edited Feb 07 '23 at 12:36

answered Feb 07 '23 at 11:52

Thom A

88,727
11
45
75

Hi Larnu, thank you for your answer. I don't used "WHEN T1.[Table 1] + '1'" as the values is sometimes not similar to column names. Used it as my basis and I think my sql is good enough for my requirement. Please check my edits for the sql I made. Again thank you. – Seigfred Mondez Feb 08 '23 at 10:39

Seigfred Mondez · Answer 2 · 2023-02-08T11:14:46.927

As response to Larnu's answer, [added Table 2 Col 3] that can be used on sorting. Sorry don't know it exists.

Also after using Larnu's answer I came up with the following SQL which is I think good enough for my requirement. Big thanks to Larnu's help.

SELECT 
T1.[Table 1] AS [Output Col 1],
(SELECT [Table 2 Col 2] FROM  dbo.YourSecondTable WHERE  dbo.YourSecondTable.[Table 2 Col 1]=T1.[Table 1] ORDER BY dbo.YourSecondTable.[Table 2 Col 3] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Output Col 1],     
(SELECT [Table 2 Col 2] FROM  dbo.YourSecondTable WHERE  dbo.YourSecondTable.[Table 2 Col 1]=T1.[Table 1] ORDER BY dbo.YourSecondTable.[Table 2 Col 3] OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS [Output Col 2],     
(SELECT [Table 2 Col 2] FROM  dbo.YourSecondTable WHERE  dbo.YourSecondTable.[Table 2 Col 1]=T1.[Table 1] ORDER BY dbo.YourSecondTable.[Table 2 Col 3] OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS [Output Col 3]  
FROM dbo.YourFirstTable T1
LEFT JOIN dbo.YourSecondTable T2 ON T1.[Table 1] = T2.[Table 2 Col 1]
GROUP BY T1.[Table 1];

score -1 · Answer 3 · answered Feb 07 '23 at 20:51

since you have an unknown number of columns (I believe that SQL Server limits to columns is 1000) I would build a dynamic "pivot" using dynamic SQL somthing like this:

DROP TABLE IF EXISTS table_1
DROP TABLE IF EXISTS table_2
CREATE TABLE table_1([Col 1] VARCHAR(2) )
CREATE TABLE table_2([Col 1] VARCHAR(2) , [col 2] VARCHAR(2))

TRUNCATE TABLE Table_1
TRUNCATE TABLE Table_2
INSERT INTO TABLE_1([col 1])  VALUES ('A'),('B'),('C'),('D')
INSERT INTO TABLE_2([col 1], [col 2])  
VALUES ('A','A1'),('A','A2'),('A','A3'),('B','B1')
DROP TABLE IF EXISTS #Dict

;WITH cte1 AS (
    SELECT DISTINCT [col 1] , [col 2]
    FROM table_2
), cte2 AS (
SELECT [col 1], [col 2], ROW_NUMBER() OVER (PARTITION BY [Col 1] ORDER BY [Col 1],[col 2]) ColId
FROM cte1
)
SELECT DISTINCT *
INTO #Dict
FROM cte2
WHERE ColId < 999
--select * from #Dict

DROP TABLE IF EXISTS #Pivot 
DECLARE @SQLCmdCreate NVARCHAR(max) = 'CREATE TABLE #Pivot ([Table_1 Col 1] VARCHAR(2), '
DECLARE @SQLCmdInsert NVARCHAR(max) = 'INSERT INTO #Pivot ( [Table_1 Col 1], '
DECLARE @SQLCmdSelect NVARCHAR(max) = 'SELECT Table_1.[Col 1], '
DECLARE @SQLCmd       NVARCHAR(max) 
DECLARE @q NCHAR(1) = ''''
DECLARE @crlf NCHAR(2) = CHAR(13)+ CHAR(10)
SELECT @SQLCmdCreate += STRING_AGG(CONCAT('[Output ', ColId, '] VARCHAR(2)'), ',')
     , @SQLCmdInsert += STRING_AGG(CONCAT('[Output ', ColId, ']'), ',')
     , @SQLCmdSelect += STRING_AGG(CONCAT(@crlf, 'MIN(CASE WHEN [ColId] = ', [ColId], ' THEN Dict.[Col 2] END) '), ',') 
FROM (
    SELECT DISTINCT TOP 100 PERCENT ColId FROM #Dict ORDER BY ColId
) dict


SELECT @SQLCmdCreate+= ')'
   , @SQLCmdInsert += ')'
   , @SQLCmdSelect += @crlf + 'FROM Table_1 '
                    + 'left join #Dict dict ' 
                    + '    on  Dict.[Col 1] = Table_1.[Col 1]' 
                    + @crlf + 'GROUP BY table_1.[Col 1]'

SELECT @SQLCmd = @SQLCmdCreate + @crlf  + @SQLCmdInsert +  @crlf + @SQLCmdSelect + @crlf + 'SELECT * FROM #Pivot ORDER BY [Table_1 Col 1]'
--print @SQLCmd
EXEC (@SQLCmd)

SQL Server T-SQL Rows to Columns

3 Answers3