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 (3)

  1. John Ingres says:

    Quite ugly 🙁

    Use simple CASE statements in the projection list. It will be orders of magnitude faster and will consume less server memory.

  2. irenake says:

    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.

  3. indianitguru says:

    Wont the cursors slow down the execution time.

Skip to main content