-- CREATE TEST DATA create table #main ( Id INT IDENTITY, [Description] VARCHAR(90))create table #attributes( Id INT, FieldName VARCHAR(128), FieldValue VARCHAR(MAX))declare @i int set @i = 1declare @c int declare @Id int WHILE @i <= 10000BEGIN INSERT INTO #main(Description) Values ('Description ' + right('00000' + convert(varchar(5), @i),5)) SET @Id = scope_identity() set @c = 1 WHILE @c <= 183 BEGIN INSERT INTO #attributes ( Id , FieldName , FieldValue ) VALUES ( @Id , 'Field' + right('000' + convert(varchar(3), @c), 3) , 'FieldValue' + right('000' + convert(varchar(3), @c), 3) + 'Description' + right('00000' + convert(varchar(5), @i), 5) ) set @c = @c + 1 END set @i = @i + 1END
SELECT M.Id, M.Description, MIN(case when FieldName = 'Field001' then FieldValue END), MIN(case when FieldName = 'Field002' then FieldValue END), MIN(case when FieldName = 'Field003' then FieldValue END), MIN(case when FieldName = 'Field004' then FieldValue END), MIN(case when FieldName = 'Field005' then FieldValue END), MIN(case when FieldName = 'Field006' then FieldValue END), MIN(case when FieldName = 'Field007' then FieldValue END), MIN(case when FieldName = 'Field008' then FieldValue END), MIN(case when FieldName = 'Field009' then FieldValue END), MIN(case when FieldName = 'Field010' then FieldValue END), MIN(case when FieldName = 'Field011' then FieldValue END), MIN(case when FieldName = 'Field012' then FieldValue END), MIN(case when FieldName = 'Field013' then FieldValue END), MIN(case when FieldName = 'Field014' then FieldValue END), MIN(case when FieldName = 'Field015' then FieldValue END), MIN(case when FieldName = 'Field016' then FieldValue END), MIN(case when FieldName = 'Field017' then FieldValue END), MIN(case when FieldName = 'Field018' then FieldValue END), MIN(case when FieldName = 'Field019' then FieldValue END), MIN(case when FieldName = 'Field020' then FieldValue END), MIN(case when FieldName = 'Field021' then FieldValue END), MIN(case when FieldName = 'Field022' then FieldValue END), MIN(case when FieldName = 'Field023' then FieldValue END), MIN(case when FieldName = 'Field024' then FieldValue END), MIN(case when FieldName = 'Field025' then FieldValue END), MIN(case when FieldName = 'Field026' then FieldValue END), MIN(case when FieldName = 'Field027' then FieldValue END), MIN(case when FieldName = 'Field028' then FieldValue END), MIN(case when FieldName = 'Field029' then FieldValue END), MIN(case when FieldName = 'Field030' then FieldValue END), MIN(case when FieldName = 'Field031' then FieldValue END), MIN(case when FieldName = 'Field032' then FieldValue END), MIN(case when FieldName = 'Field033' then FieldValue END), MIN(case when FieldName = 'Field034' then FieldValue END), MIN(case when FieldName = 'Field035' then FieldValue END), MIN(case when FieldName = 'Field036' then FieldValue END), MIN(case when FieldName = 'Field037' then FieldValue END), MIN(case when FieldName = 'Field038' then FieldValue END), MIN(case when FieldName = 'Field039' then FieldValue END), MIN(case when FieldName = 'Field040' then FieldValue END), MIN(case when FieldName = 'Field041' then FieldValue END), MIN(case when FieldName = 'Field042' then FieldValue END), MIN(case when FieldName = 'Field043' then FieldValue END), MIN(case when FieldName = 'Field044' then FieldValue END), MIN(case when FieldName = 'Field045' then FieldValue END), MIN(case when FieldName = 'Field046' then FieldValue END), MIN(case when FieldName = 'Field047' then FieldValue END), MIN(case when FieldName = 'Field048' then FieldValue END), MIN(case when FieldName = 'Field049' then FieldValue END), MIN(case when FieldName = 'Field050' then FieldValue END), MIN(case when FieldName = 'Field051' then FieldValue END), MIN(case when FieldName = 'Field052' then FieldValue END), MIN(case when FieldName = 'Field053' then FieldValue END), MIN(case when FieldName = 'Field054' then FieldValue END), MIN(case when FieldName = 'Field055' then FieldValue END), MIN(case when FieldName = 'Field056' then FieldValue END), MIN(case when FieldName = 'Field057' then FieldValue END), MIN(case when FieldName = 'Field058' then FieldValue END), MIN(case when FieldName = 'Field059' then FieldValue END), MIN(case when FieldName = 'Field060' then FieldValue END), MIN(case when FieldName = 'Field061' then FieldValue END), MIN(case when FieldName = 'Field062' then FieldValue END), MIN(case when FieldName = 'Field063' then FieldValue END), MIN(case when FieldName = 'Field064' then FieldValue END), MIN(case when FieldName = 'Field065' then FieldValue END), MIN(case when FieldName = 'Field066' then FieldValue END), MIN(case when FieldName = 'Field067' then FieldValue END), MIN(case when FieldName = 'Field068' then FieldValue END), MIN(case when FieldName = 'Field069' then FieldValue END), MIN(case when FieldName = 'Field070' then FieldValue END), MIN(case when FieldName = 'Field071' then FieldValue END), MIN(case when FieldName = 'Field072' then FieldValue END), MIN(case when FieldName = 'Field073' then FieldValue END), MIN(case when FieldName = 'Field074' then FieldValue END), MIN(case when FieldName = 'Field075' then FieldValue END), MIN(case when FieldName = 'Field076' then FieldValue END), MIN(case when FieldName = 'Field077' then FieldValue END), MIN(case when FieldName = 'Field078' then FieldValue END), MIN(case when FieldName = 'Field079' then FieldValue END), MIN(case when FieldName = 'Field080' then FieldValue END), MIN(case when FieldName = 'Field081' then FieldValue END), MIN(case when FieldName = 'Field082' then FieldValue END), MIN(case when FieldName = 'Field083' then FieldValue END), MIN(case when FieldName = 'Field084' then FieldValue END), MIN(case when FieldName = 'Field085' then FieldValue END), MIN(case when FieldName = 'Field086' then FieldValue END), MIN(case when FieldName = 'Field087' then FieldValue END), MIN(case when FieldName = 'Field088' then FieldValue END), MIN(case when FieldName = 'Field089' then FieldValue END), MIN(case when FieldName = 'Field090' then FieldValue END), MIN(case when FieldName = 'Field091' then FieldValue END), MIN(case when FieldName = 'Field092' then FieldValue END), MIN(case when FieldName = 'Field093' then FieldValue END), MIN(case when FieldName = 'Field094' then FieldValue END), MIN(case when FieldName = 'Field095' then FieldValue END), MIN(case when FieldName = 'Field096' then FieldValue END), MIN(case when FieldName = 'Field097' then FieldValue END), MIN(case when FieldName = 'Field098' then FieldValue END), MIN(case when FieldName = 'Field099' then FieldValue END), MIN(case when FieldName = 'Field100' then FieldValue END), MIN(case when FieldName = 'Field101' then FieldValue END), MIN(case when FieldName = 'Field102' then FieldValue END), MIN(case when FieldName = 'Field103' then FieldValue END), MIN(case when FieldName = 'Field104' then FieldValue END), MIN(case when FieldName = 'Field105' then FieldValue END), MIN(case when FieldName = 'Field106' then FieldValue END), MIN(case when FieldName = 'Field107' then FieldValue END), MIN(case when FieldName = 'Field108' then FieldValue END), MIN(case when FieldName = 'Field109' then FieldValue END), MIN(case when FieldName = 'Field110' then FieldValue END), MIN(case when FieldName = 'Field111' then FieldValue END), MIN(case when FieldName = 'Field112' then FieldValue END), MIN(case when FieldName = 'Field113' then FieldValue END), MIN(case when FieldName = 'Field114' then FieldValue END), MIN(case when FieldName = 'Field115' then FieldValue END), MIN(case when FieldName = 'Field116' then FieldValue END), MIN(case when FieldName = 'Field117' then FieldValue END), MIN(case when FieldName = 'Field118' then FieldValue END), MIN(case when FieldName = 'Field119' then FieldValue END), MIN(case when FieldName = 'Field120' then FieldValue END), MIN(case when FieldName = 'Field121' then FieldValue END), MIN(case when FieldName = 'Field122' then FieldValue END), MIN(case when FieldName = 'Field123' then FieldValue END), MIN(case when FieldName = 'Field124' then FieldValue END), MIN(case when FieldName = 'Field125' then FieldValue END), MIN(case when FieldName = 'Field126' then FieldValue END), MIN(case when FieldName = 'Field127' then FieldValue END), MIN(case when FieldName = 'Field128' then FieldValue END), MIN(case when FieldName = 'Field129' then FieldValue END), MIN(case when FieldName = 'Field130' then FieldValue END), MIN(case when FieldName = 'Field131' then FieldValue END), MIN(case when FieldName = 'Field132' then FieldValue END), MIN(case when FieldName = 'Field133' then FieldValue END), MIN(case when FieldName = 'Field134' then FieldValue END), MIN(case when FieldName = 'Field135' then FieldValue END), MIN(case when FieldName = 'Field136' then FieldValue END), MIN(case when FieldName = 'Field137' then FieldValue END), MIN(case when FieldName = 'Field138' then FieldValue END), MIN(case when FieldName = 'Field139' then FieldValue END), MIN(case when FieldName = 'Field140' then FieldValue END), MIN(case when FieldName = 'Field141' then FieldValue END), MIN(case when FieldName = 'Field142' then FieldValue END), MIN(case when FieldName = 'Field143' then FieldValue END), MIN(case when FieldName = 'Field144' then FieldValue END), MIN(case when FieldName = 'Field145' then FieldValue END), MIN(case when FieldName = 'Field146' then FieldValue END), MIN(case when FieldName = 'Field147' then FieldValue END), MIN(case when FieldName = 'Field148' then FieldValue END), MIN(case when FieldName = 'Field149' then FieldValue END), MIN(case when FieldName = 'Field150' then FieldValue END), MIN(case when FieldName = 'Field151' then FieldValue END), MIN(case when FieldName = 'Field152' then FieldValue END), MIN(case when FieldName = 'Field153' then FieldValue END), MIN(case when FieldName = 'Field154' then FieldValue END), MIN(case when FieldName = 'Field155' then FieldValue END), MIN(case when FieldName = 'Field156' then FieldValue END), MIN(case when FieldName = 'Field157' then FieldValue END), MIN(case when FieldName = 'Field158' then FieldValue END), MIN(case when FieldName = 'Field159' then FieldValue END), MIN(case when FieldName = 'Field160' then FieldValue END), MIN(case when FieldName = 'Field161' then FieldValue END), MIN(case when FieldName = 'Field162' then FieldValue END), MIN(case when FieldName = 'Field163' then FieldValue END), MIN(case when FieldName = 'Field164' then FieldValue END), MIN(case when FieldName = 'Field165' then FieldValue END), MIN(case when FieldName = 'Field166' then FieldValue END), MIN(case when FieldName = 'Field167' then FieldValue END), MIN(case when FieldName = 'Field168' then FieldValue END), MIN(case when FieldName = 'Field169' then FieldValue END), MIN(case when FieldName = 'Field170' then FieldValue END), MIN(case when FieldName = 'Field171' then FieldValue END), MIN(case when FieldName = 'Field172' then FieldValue END), MIN(case when FieldName = 'Field173' then FieldValue END), MIN(case when FieldName = 'Field174' then FieldValue END), MIN(case when FieldName = 'Field175' then FieldValue END), MIN(case when FieldName = 'Field176' then FieldValue END), MIN(case when FieldName = 'Field177' then FieldValue END), MIN(case when FieldName = 'Field178' then FieldValue END), MIN(case when FieldName = 'Field179' then FieldValue END), MIN(case when FieldName = 'Field180' then FieldValue END), MIN(case when FieldName = 'Field181' then FieldValue END), MIN(case when FieldName = 'Field182' then FieldValue END), MIN(case when FieldName = 'Field183' then FieldValue END)from #main MINNER JOIN #attributes A ON M.ID = A.ID GROUP BY M.Id, m.Description
