Assume this is our table:
CREATE TABLE dbo.SomeTable
(
Id INT NOT NULL,
Name VARCHAR(100) NOT NULL,
[Description] NVARCHAR(max) NULL,
Comments NVARCHAR(max) NULL,
CONSTRAINT PK_SomeTable PRIMARY KEY (Id)
);
If the number of rows and columns is always fixed, this example will work:
DECLARE @flatenedTable TABLE
(
Id_1 INT NULL,
Id_2 INT NULL,
Id_3 INT NULL,
Id_4 INT NULL,
Name_1 VARCHAR(100) NULL,
Name_2 VARCHAR(100) NULL,
Name_3 VARCHAR(100) NULL,
Name_4 VARCHAR(100) NULL,
Description_1 VARCHAR(MAX) NULL,
Description_2 VARCHAR(MAX) NULL,
Description_3 VARCHAR(MAX) NULL,
Description_4 VARCHAR(MAX) NULL,
Comments_1 VARCHAR(MAX) NULL,
Comments_2 VARCHAR(MAX) NULL,
Comments_3 VARCHAR(MAX) NULL,
Comments_4 VARCHAR(MAX) NULL
);
DECLARE @startingId INT
SELECT
@startingId = MIN(Id)
FROM dbo.SomeTable
INSERT INTO @flatenedTable (
Id_1,
Id_2,
Id_3,
Id_4,
Name_1,
Name_2,
Name_3,
Name_4,
Description_1,
Description_2,
Description_3,
Description_4,
Comments_1,
Comments_2,
Comments_3,
Comments_4
)
SELECT
CASE WHEN Id = @startingId + 0 THEN Id ELSE NULL END AS Id_1,
CASE WHEN Id = @startingId + 1 THEN Id ELSE NULL END AS Id_2,
CASE WHEN Id = @startingId + 2 THEN Id ELSE NULL END AS Id_3,
CASE WHEN Id = @startingId + 3 THEN Id ELSE NULL END AS Id_4,
CASE WHEN Id = @startingId + 0 THEN Name ELSE NULL END AS Name_1,
CASE WHEN Id = @startingId + 1 THEN Name ELSE NULL END AS Name_2,
CASE WHEN Id = @startingId + 2 THEN Name ELSE NULL END AS Name_3,
CASE WHEN Id = @startingId + 3 THEN Name ELSE NULL END AS Name_4,
CASE WHEN Id = @startingId + 0 THEN [Description] ELSE NULL END AS Description_1,
CASE WHEN Id = @startingId + 1 THEN [Description] ELSE NULL END AS Description_2,
CASE WHEN Id = @startingId + 2 THEN [Description] ELSE NULL END AS Description_3,
CASE WHEN Id = @startingId + 3 THEN [Description] ELSE NULL END AS Description_4,
CASE WHEN Id = @startingId + 0 THEN [Comments] ELSE NULL END AS Comments_1,
CASE WHEN Id = @startingId + 1 THEN [Comments] ELSE NULL END AS Comments_2,
CASE WHEN Id = @startingId + 2 THEN [Comments] ELSE NULL END AS Comments_3,
CASE WHEN Id = @startingId + 3 THEN [Comments] ELSE NULL END AS Comments_4
FROM dbo.SomeTable
SELECT
MAX(Id_1) AS Id_1,
MAX(Id_2) AS Id_2,
MAX(Id_3) AS Id_3,
MAX(Id_4) AS Id_4,
MAX(Name_1) AS Name_1,
MAX(Name_2) AS Name_2,
MAX(Name_3) AS Name_3,
MAX(Name_4) AS Name_4,
MAX(Description_1) AS Description_1,
MAX(Description_2) AS Description_2,
MAX(Description_3) AS Description_3,
MAX(Description_4) AS Description_4,
MAX(Comments_1) AS Comments_1,
MAX(Comments_2) AS Comments_2,
MAX(Comments_3) AS Comments_3,
MAX(Comments_4) AS Comments_4
FROM @flatenedTable
However, if they are not fixed, you will need to used dynamic SQL to achieve this:
DECLARE @numberOfRows INT;
DECLARE @index INT = 1;
DECLARE @newLine CHAR(2) = CHAR(13) + CHAR(10);
SELECT
@numberOfRows = COUNT(*)
FROM dbo.SomeTable
DECLARE @variableTableSql NVARCHAR(MAX) = N'DECLARE @flatenedTable TABLE' + @newLine + '(' + @newLine;
DECLARE @selectStatement NVARCHAR(MAX) = N'SELECT' + @newLine;
DECLARE @aggregateSelect NVARCHAR(MAX) = N'SELECT' + @newLine;
WHILE @index <= @numberOfRows
BEGIN
SET @variableTableSql = CONCAT(@variableTableSql,
CHAR(9), N'Id_', @index, N' INT NULL,', @newLine,
CHAR(9), N'Name_', @index, N' VARCHAR(100) NULL,', @newLine,
CHAR(9), N'Description_', @index, N' VARCHAR(MAX) NULL,' + @newLine,
CHAR(9), N'Comments_', @index, N' VARCHAR(MAX) NULL');
SET @selectStatement = CONCAT(@selectStatement,
CHAR(9), N'CASE WHEN Id = ', @index, N' THEN Id ELSE NULL END AS Id_', @index, N',', @newLine,
CHAR(9), N'CASE WHEN Id = ', @index, N' THEN Name ELSE NULL END AS Name_', @index, N',', @newLine,
CHAR(9), N'CASE WHEN Id = ', @index, N' THEN [Description] ELSE NULL END AS Description_', @index, N',', @newLine,
CHAR(9), N'CASE WHEN Id = ', @index, N' THEN [Comments] ELSE NULL END AS Comments_', @index);
SET @aggregateSelect = CONCAT(@aggregateSelect,
CHAR(9), 'MAX(Id_', @index, N') AS Id_', @index, N',', @newLine,
CHAR(9), 'MAX(Name_', @index, N') AS Name_', @index, N',', @newLine,
CHAR(9), 'MAX(Description_', @index, N') AS Description_', @index, N',', @newLine,
CHAR(9), 'MAX(Comments_', @index, N') AS Comments_', @index);
IF @index != @numberOfRows
BEGIN
SET @variableTableSql = CONCAT(@variableTableSql, N',', @newLine);
SET @selectStatement = CONCAT(@selectStatement, N',', @newLine);
SET @aggregateSelect = CONCAT(@aggregateSelect, N',', @newLine);
END
ELSE
BEGIN
SET @variableTableSql = CONCAT(@variableTableSql, @newLine, ');');
SET @selectStatement = CONCAT(@selectStatement, @newLine, 'FROM dbo.SomeTable');
SET @aggregateSelect = CONCAT(@aggregateSelect, @newLine, 'FROM @flatenedTable');
END
SET @index = @index + 1
END
DECLARE @sql NVARCHAR(MAX);
SET @sql = CONCAT(
@variableTableSql,
@newLine,
@newLine,
'INSERT INTO @flatenedTable',
@newLine,
@selectStatement,
@newLine,
@newLine,
@aggregateSelect);
EXEC sp_executesql @sql