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
(
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')