SQL Pivot with two columns
I'm trying to make a PIVOT on a table with two lines like this:
Category | Sector
---------------------------
Bulbs | DIY
Bulbs | Home
Picnic blanket | DIY
Picnic blanket | Home
Picnic blanket | Interior
Each category can have 1 or more sectors.
I am trying to get a table like this:
Category | Sector 1 | Sector 2 | Sector 3
-------------------------------------------------
Bulbs | DIY | Home | NULL
Picnic blanket | DIY | Home | Interior
The request looks like this:
SELECT
dbo.fn_DbContent_GetTranslation(pt.Name_DbContentId, 2) 'Category'
, dbo.fn_DbContent_GetTranslation(s.Name_DbContentId, 2) 'Sector'
FROM dbo.ProductType pt
JOIN dbo.ProductTypeMandator ptm ON ptm.ProductTypeId = pt.Id
JOIN dbo.ProductTypeMandator2PortalSector ptmps ON ptmps.ProductTypeMandatorId = ptm.Id
JOIN dbo.PortalSector ps ON ps.Id = ptmps.PortalSectorId
JOIN dbo.Sector s ON s.Id = ps.SectorId
WHERE
ptmps.PortalSectorId IN (21, 18, 19)
I already did PIVOT, but with a table containing three columns that contained values โโfor the headers in the pivot table. In this case, the values โโfor the title are missing, so I don't know how to do this.
thanks for the help
source to share
You can solve it like this:
-- Create demo data
CREATE TABLE #cats(category nvarchar(25), sector nvarchar(25))
INSERT INTO #cats(category, sector)
VALUES (N'Bulbs',N'DIY'),
(N'Bulbs',N'Home'),
(N'Picnic blanket',N'DIY'),
(N'Picnic blanket',N'Home'),
(N'Picnic blanket',N'Interior')
SELECT *
FROM (
SELECT category, sector
FROM #cats
) as dat
PIVOT(
MAX(sector)
FOR sector IN([DIY],[Home],[Interior])
) as pvt
-- Cleanup
DROP TABLE #cats
If you want it to be properly named, as in your example, you can combine it with named numbering:
-- Create demo data
CREATE TABLE #cats(category nvarchar(25), sector nvarchar(25))
INSERT INTO #cats(category, sector)
VALUES (N'Bulbs',N'DIY'),
(N'Bulbs',N'Home'),
(N'Picnic blanket',N'DIY'),
(N'Picnic blanket',N'Home'),
(N'Picnic blanket',N'Interior')
SELECT *
FROM (
SELECT category, sector, N'Sector '+CONVERT(nvarchar(max),DENSE_RANK() OVER(ORDER BY sector)) as rn
FROM #cats
) as dat
PIVOT(
MAX(sector)
FOR rn IN([Sector 1],[Sector 2],[Sector 3])
) as pvt
-- Cleanup
DROP TABLE #cats
Last but not least, if you want it to be dynamic:
-- Create demo data
CREATE TABLE #cats(category nvarchar(25), sector nvarchar(25))
INSERT INTO #cats(category, sector)
VALUES (N'Bulbs',N'DIY'),
(N'Bulbs',N'Home'),
(N'Picnic blanket',N'DIY'),
(N'Picnic blanket',N'Home'),
(N'Picnic blanket',N'Interior')
DECLARE @sql nvarchar(max), @cols nvarchar(max)
-- get proper column list
SELECT @cols = COALESCE(@cols + N',[' + grCols.rn + N']',N'[' + grCols.rn + N']')
FROM (
SELECT DISTINCT N'Sector '+CONVERT(nvarchar(max),DENSE_RANK() OVER(ORDER BY sector)) as rn
FROM #cats
) as grCols
SET @sql = N'
SELECT *
FROM (
SELECT category, sector, N''Sector ''+CONVERT(nvarchar(max),DENSE_RANK() OVER(ORDER BY sector)) as rn
FROM #cats
) as dat
PIVOT(
MAX(sector)
FOR rn IN('+@cols+')
) as pvt'
EXEC(@sql)
-- Cleanup
DROP TABLE #cats
source to share
You can do this in two ways:
One Conditional Aggregate
;WITH cte
AS (SELECT *,
rn=Row_number()
OVER(
partition BY category
ORDER BY (SELECT NULL))
FROM yourtable)
SELECT category,
[sector 1] = Max(CASE WHEN rn = 1 THEN sector END),
[sector 2] = Max(CASE WHEN rn = 2 THEN sector END),
[sector 3] = Max(CASE WHEN rn = 3 THEN sector END)
FROM cte
GROUP BY category
Another way is to use Pivot
SELECT *
FROM (SELECT *,
sector_col = 'sector '
+ Cast(Row_number()OVER(partition BY category ORDER BY (SELECT NULL)) AS VARCHAR(20))
FROM yourtable) A
PIVOT (Max(sector)
FOR sector_col IN ([sector 1],
[sector 2],
[sector 3])) piv
Note: As mentioned in the comments, if the values sector
are not static
or unknown
, then you can convert them todynamic query
source to share