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