How to use pivot for columns of type varchar

Question

My table is:

SBType|SBName|Qty
===================
SMDB    SB01    1
SMDB    SB01    4
SMDB    SB02    2
SMDB    SB02    5
SMDB    SB03    3
SMDB    SB03    6

My desired output is:

SB01 | SB02 | SB03
==================
1       2       3 
4       5       6

This is what my code looks like:

SELECT *
FROM (
SELECT 
    SM.SBName,ISNULL(ES.Qty,0)Qty
FROM RE_ES_SwitchBoard_Mast SM
left outer join RE_ES_Estimations ES on SM.PrCode=ES.PrCode and 
Sm.SBType=ES.SBType and SM.SBName=ES.SBName 
 Where SM.PrCode='PR004' and SM.SBType='SMDB'
) as s
PIVOT
(
Max(Qty)
FOR [SBName] IN (SB01, SB02, SB03)
)AS pvthere

and the result of my attempt looks like:

SB01    SB02    SB03
  1     2       3

I have tried with MAX(Qty) but it is not working. Thanks in advance.

**SQL** is a query language and a language standard - **NOT** a database product. You probably mean **SQL Server** - please use that official product name to be clear and avoid confusion — marc_s, Aug 08 '17 at 06:41

J-D · Answer 1 · 2017-08-08T10:31:26.263

Dynamic query is the only way to use varchar columns in pivot. Have a look at below code to get idea.

First step is to generate comma separated list of items for column you need to use in pivot.

Then you can use this generated list in dynamic query for pivot columns.

Note: For example purpose I have used temp table. Replace it with your actual table.

CREATE TABLE  #temptable
(
    SBType VARCHAR(20),
    SBName VARCHAR(20),
    Qty INT
)

INSERT INTO #temptable SELECT 'SMDB','SB01',1
INSERT INTO #temptable SELECT 'SMDB','SB01',4
INSERT INTO #temptable SELECT 'SMDB','SB02',2
INSERT INTO #temptable SELECT 'SMDB','SB02',5
INSERT INTO #temptable SELECT 'SMDB','SB03',3
INSERT INTO #temptable SELECT 'SMDB','SB03',6

SELECT * FROM #temptable

DECLARE @cols AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(SBName) 
                    from #temptable
                    group by SBName
                    order by SBName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SELECT @cols

DECLARE @query NVARCHAR(MAX)
SET @query = '
SELECT *
FROM 
(
  SELECT SBType,SBName,Qty,
  row_number() over (partition by SBName order by Qty) as rn
  FROM #temptable
) src
PIVOT
(
  MIN(Qty)
  FOR SBName IN (' + @cols + ')
) piv;'

EXEC(@query)

DROP TABLE #temptable

Output does not match with the desired output in the question. — Vojtěch Dohnal, Aug 08 '17 at 07:31
[Link to the source answer](https://stackoverflow.com/a/15745076/2224701). — Vojtěch Dohnal, Aug 08 '17 at 09:46
@Vojtěch Dohnal : Answer edited to have a desire output with dynamic sql. — J-D, Aug 08 '17 at 10:17
I suggest that you quote the sources that you have used in your answer. It is apparently [this answer](https://stackoverflow.com/a/15745076/2224701) and [this answer](https://stackoverflow.com/a/45565143/2224701) combined. It is considered a good habit. — Vojtěch Dohnal, Aug 08 '17 at 10:42

Vojtěch Dohnal · Answer 2 · 2017-08-08T10:04:16.590

You are almost there. By adding ROW_NUMBER() OVER (PARTITION BY SBName ORDER BY Qty) rn to the source of PIVOT clause you get multiple rows for different SBName instead of one grouped row. Your query should look like:

SELECT SB01, SB02, SB03
FROM (
SELECT 
    ROW_NUMBER() OVER (PARTITION BY SB.SBName ORDER BY Qty) rn,
    SB.SBName,ISNULL(ES.Qty,0) Qty
    FROM RE_ES_SwitchBoard_Mast SM
    left outer join RE_ES_Estimations ES on SM.PrCode=ES.PrCode and 
    Sm.SBType=ES.SBType and SM.SBName=ES.SBName 
    Where SM.PrCode='PR004' and SM.SBType='SMDB'
) as s
PIVOT
(
    Max(Qty)
    FOR [SBName] IN (SB01, SB02, SB03)
)AS pvthere

A verifiable example here:

CREATE TABLE  #sample
(
    SBType varchar(MAX),
    SBName varchar(MAX),
    Qty int
)

INSERT INTO #sample VALUES ('SMDB','SB01',1)
INSERT INTO #sample VALUES ('SMDB','SB01',4)
INSERT INTO #sample VALUES ('SMDB','SB02',2)
INSERT INTO #sample VALUES ('SMDB','SB02',5)
INSERT INTO #sample VALUES ('SMDB','SB03',3)
INSERT INTO #sample VALUES ('SMDB','SB03',6)

SELECT SB01, SB02, SB03
FROM (
SELECT 
    ROW_NUMBER() OVER (PARTITION BY SBName ORDER BY Qty) rn, SBName,ISNULL(Qty,0) Qty
    FROM #sample 
) as s
PIVOT
(
   Max(Qty)
FOR [SBName] IN (SB01, SB02, SB03)
) AS pvthere

DROP TABLE #sample

How to use pivot for columns of type varchar

2 Answers2