SYSK 55: Rotate SQL table without any INNER JOIN (narrow to wide table without PIVOT)

If you have a narrow table, e.g. just the following columns -- GroupId, DataId, DataValue (frequently used to store facts, e.g. questions and answers) – but you want to get all data ids and values for a given group id as one row, try the SQL below:

CREATE TABLE #Data ([GroupId] [bigint] NOT NULL);
INSERT INTO #Data (GroupId) VALUES (25598);

declare @sql1 nvarchar(4000), @sql2 nvarchar(4000);
set @sql1 = '';
set @sql2 = '';
declare @id int;
declare @value varchar(256);
DECLARE c CURSOR FOR SELECT DataId, DataValue FROM Table_1 WHERE GroupId = @GroupId
OPEN c;
FETCH NEXT FROM c into @id, @value;
WHILE @@FETCH_STATUS = 0
BEGIN
if (len(@sql1) > 0)
set @sql1 = @sql1 + ', ';
set @sql1 = @sql1 + 'Data' + cast(@id as varchar(256)) + ' varchar(256) NULL'; 

 if (len(@sql2) > 0)
set @sql2 = @sql2 + ', '
set @sql2 = @sql2 + 'Data' + cast(@id as varchar(256)) + ' = ''' + @value + '''';

if len(@sql1) >= 3500 or len(@sql2) >= 3500
begin
set @sql1 = 'ALTER TABLE #Data ADD ' + @sql1
set @sql2 = 'UPDATE #Data SET ' + @sql2
EXEC sp_executesql @sql1;
EXEC sp_executesql @sql2;
set @sql1 = '';
set @sql2 = '';
end

 FETCH NEXT FROM c into @id, @value;
END

close c;
DEALLOCATE c;

set @sql1 = 'ALTER TABLE #Data ADD ' + @sql1
set @sql2 = 'UPDATE #Data SET ' + @sql2

PRINT @sql1

EXEC sp_executesql @sql1;

print @sql2
EXEC sp_executesql @sql2;

select * from #data
drop table #data

Comments

• Anonymous
  February 24, 2006
  Quite ugly :-(
  
  Use simple CASE statements in the projection list. It will be orders of magnitude faster and will consume less server memory.
• Anonymous
  February 26, 2006
  Using CASE assumes you know, at the time of writing the SQL statement, what data columns are in your table.  There are many applications where this is not the case.
• Anonymous
  December 05, 2007
  The comment has been removed