i want the help in CTE to display header name from the table value

Question

I am trying to fetch few records from the table. I have one category mapped to multiple subcategory. I want header name as subcategory description of each record.

I have done below and splitted category with row number.

DROP TABLE #temp
CREATE TABLE #temp ( [Description] VARCHAR(100), SubDescription VARCHAR(100), IsOMExceptionRequired bit,IsAMB bit,IsARS bit,escalationtypeid int, CategorySortOrder int, SubcategorySortorder int);

INSERT INTO #temp ([Description], SubDescription, IsOMExceptionRequired ,IsAMB,IsARS,escalationtypeid,CategorySortOrder,SubcategorySortorder)
SELECT C.Description,s.Description, S.IsOMExceptionRequired,s.IsAMB,s.IsARS,CEM.escalationtypeid, c.SortOrder,s.SortOrder
FROM category C
INNER JOIN SubCategory S on C.CategoryID =S.CategoryID 
INNER JOIN CategoryEscalationTypeMap CEM on CEM.CategoryID= C.CategoryID
WHERE CEM.escalationtypeid=3 and CEM.IsActive =1 and C.IsActive=1 and S.IsActive=1
order by CEM.escalationtypeid, C.sortorder,S.sortorder; 
--select * from #temp;
WITH Temp
AS (
    SELECT
        Description,
        SubDescription,
        ROW_NUMBER() OVER ( PARTITION BY Description
ORDER BY Description ) rownumber,
IsOMExceptionRequired,
IsAMB,
IsARS,
escalationtypeid,
CategorySortOrder,
SubcategorySortorder
    FROM #Temp
)
SELECT
    CASE WHEN Temp.rownumber = 1 THEN Description ELSE '' END [Description],
    rownumber,
    SubDescription,
    CASE WHEN IsOMExceptionRequired = 1 THEN 'YES' ELSE 'N/A' END xxx,
IsAMB,
IsARS,
escalationtypeid,
CategorySortOrder,
SubcategorySortorder
FROM Temp
order by escalationtypeid,CategorySortOrder,SubcategorySortorder;

but actually my result has to be like below.

I have to achieve this through sql, any answers appreciated... — Revathi, Oct 30 '19 at 12:55
Then use [Blah blah] for column names, but that would be an odd thing to do. — Cetin Basoz, Oct 30 '19 at 17:07
Assuming your descriptions are fixed, you can use [`PIVOT`](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15). If they are not fixed you will need to implement [dynamic pivot](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Alex, Oct 30 '19 at 23:42
Thank You Alex, It was perfectly achieved through dynamic pivot. The link you provided was very useful. — Revathi, Oct 31 '19 at 10:15

score 0 · Answer 1 · answered Oct 30 '19 at 14:50

0

If you really need to return dynamic column names, build a dynamic query. Create an nvarchar that contains your query, drawing the column header from the appropriate sources, and execute it with sp_executesql.

answered Oct 30 '19 at 14:50

izzy

769
2
12
22

This is too vague/generic of an answer. You should at least suggest the use of a PIVOT. – Alex Oct 30 '19 at 23:44

i want the help in CTE to display header name from the table value

1 Answers1