SQLTeam.com | Weblogs | Forums

How to Transpose ID, Description from sql result

Hi

I have data like below

ResultLevelID ResultLevelDesc
1814 National
3785 CYT/None CYT
2912 Country
2447 Business Manager
2448 Owner Operator
2106 Regional
1813 Store type
1812 Ops Manager
3765 QSC Consultant
1811 Consultant
1810 Site

and i want to transpose like below
1814 National 3785 CYT/None CYT 2912 Country 2447 Business Manager 2448 Owner Operator

I have used cursor and got below format

-- SELECT * FROM DBTST1.gapbuster_799.dbo.DTT_ResultLevel WITH (nolock) ORDER BY hierarchy Desc

DECLARE @TableName NVARCHAR(100) SET @TableName = 'DBTST1.gapbuster_' + CONVERT(NVARCHAR, @ClientId) + '.dbo.DTT_ResultLevel' 
--PRINT @TableName

CREATE TABLE #AccessLevelData 
(
	ReportID INT, 
	ReportName NVARCHAR(100)
) 

DECLARE @ResultID NVARCHAR(10) 
DECLARE @SQL NVARCHAR(MAX)
	  , @ResultLevelDesc NVARCHAR(100)
	  , @ResultLevelId INT
	  , @lv_CSQLHeader NVARCHAR(max) 

SET @lv_CSQLHeader = ' DECLARE curhdr cursor for '
SET @lv_CSQLHeader = @lv_CSQLHeader + ' Select ResultLevelDesc, '
SET @lv_CSQLHeader = @lv_CSQLHeader + ' ResultLevelId '
SET @lv_CSQLHeader = @lv_CSQLHeader + ' ,ResultLevelId AS ResultID  '
SET @lv_CSQLHeader = @lv_CSQLHeader + ' From ' + @TableName
SET @lv_CSQLHeader = @lv_CSQLHeader + ' WITH (nolock) ORDER BY hierarchy Desc' 
EXEC sp_executesql @lv_CSQLHeader 
--print @lv_CSQLHeader

OPEN curhdr 
FETCH NEXT FROM curhdr INTO @ResultLevelDesc, @ResultLevelId, @ResultID WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = ' ALTER TABLE #AccessLevelData '
	SET @SQL = @SQL + ' ADD [' + @ResultLevelDesc + '] VARCHAR(10) default ''False'' '
	EXEC sp_executesql @SQL 
	FETCH NEXT FROM curhdr INTO @ResultLevelDesc, @ResultLevelId, @ResultID
END 
CLOSE curhdr 

DEALLOCATE curhdr

SELECT * FROM #AccessLevelData

Output is below

Please help me. It's very urgent

Thanks in Advance