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
