SQLTeam.com | Weblogs | Forums

Pivot in Microsoft SQL Server Server - Query

Hi, I am new to SQL Server querying and I have a table called "mapping_tbl" which looks like the below: it has 3 columns Order_No, Name, & Generic_Name.
I have installed Management Studio to query the database and can see the table by writing:

select * from [mapping_tbl]

Order_No | Name | Generic_Name
1 | Accessories | Sub_Cat1
2 | Appliances | Sub_Cat2
etc.....

What query do I need to write in order to present the data of Generic_Name into separate columns and have a 1 row that shows Name?

Here is what the outcome should look like:

SELECT * FROM
(
SELECT
GENERIC_NAME,
NAME
FROM
[MAPPING_TBL]
) temp
PIVOT(
MAX(NAME)
FOR [GENERIC_NAME] IN (
[Sub_Cat1],
[Sub_Cat2],
[Sub_Cat3],
[Sub_Cat4],
[Sub_Cat5],
[Sub_Cat6],
[Sub_Cat7],
[Sub_Cat8],
[Sub_Cat9],
[Sub_Cat10])
) AS pivot_table;

Here's a dynamic way to do it, but not sure why each row turns into a unique column name. If there are more that 1 names assigned to a Generic Name, then this won't work since we are taking Max(Name)

IF OBJECT_ID('tempdb..#Mapping_Tbl') IS NOT NULL 
    DROP TABLE #Mapping_Tbl
create table #Mapping_Tbl(Order_no int, Name varchar(20), GenericName varchar(20))

insert into #Mapping_Tbl
values
(1, 'Accessories','Sub_Cat1'),
(2, 'Appliances','Sub_Cat2'),
(3, 'Art','Sub_Cat3'),
(4, 'Binders','Sub_Cat4'),
(5, 'Chairs','Sub_Cat5'),
(6, 'Copiers','Sub_Cat6'),
(7, 'Tables','Sub_Cat7')

--PIVOT  
DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT GenericName  
FROM #Mapping_Tbl 
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  

EXEC ('SELECT *  
FROM   
(  
        SELECT Name, GenericName 
        FROM #Mapping_Tbl
) t  
PIVOT (max(Name) FOR GenericName IN (' + @ColsList + ')) PVT')