SQL Pivot with String

SQL Pivot with String

It is giving this row because you have AddressID in the select list for you subquery "P". So even though you don't have AddressID in you top level select this, the PIVOT function is still grouping by it. You need to change this to:

SELECT  CustomerID, Firstname, Home as HomeCity, Office as OfficeCity 
FROM    (   SELECT C.CustomerID, C.FirstName, A.AddressType, A.City 
            FROM #Customer C, #Address A 
            WHERE C.CustomerID = A.CustomerID
        ) AS P
        PIVOT 
        (   MAX(city) 
            FOR AddressType in ([Home],[Office])
        ) AS  PVT

Although I would be inclined to use an explicit INNER JOIN rather than an implicit join between customer and Address.

Need to Pivot String values in SQL server

The basic PIVOT with ROW_NUMBER() will do things for you:

SELECT  [Developer],
        [Designer],
        [Coder]
FROM (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
    FROM #temp
) as t
PIVOT (
    MAX(Name) FOR Occupation IN ([Developer],[Designer],[Coder])
) as pvt

Output:

Developer   Designer    Coder
A           X           Y
B           NULL        Z

If the number of Occupations may vary then you need dynamic SQL:

DECLARE @columns nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = (
    SELECT DISTINCT ','+QUOTENAME(Occupation)
    FROM #temp
    FOR XML PATH('')
)

SELECT @sql = N'
SELECT  '+STUFF(@columns,1,1,'')+'
FROM (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
    FROM #temp
) as t
PIVOT (
    MAX(Name) FOR Occupation IN ('+STUFF(@columns,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql

Note: I have used ORDER BY (SELECT NULL) just to get some random ordering. Better use some actual field for this purpose.

TSQL Pivot with strings

If you are using SQL Server 2005+ you can do this:

SELECT
    *
FROM
(
SELECT
    PersonID,
    ContactMethod,
    ContactValue
FROM
    vwPersonMainContactDetails
) AS SourceTable
PIVOT
(
    MAX(ContactValue)
    FOR ContactMethod IN ([Email],[Mobile])
) AS pvt

If you are not using mssql you can do this:

SELECT
    PersonID,
    MAX(CASE WHEN ContactMethod='Mobile' THEN ContactValue ELSE NULL END) AS Mobile,
    MAX(CASE WHEN ContactMethod='Email' THEN ContactValue ELSE NULL END) AS Email
FROM
    vwPersonMainContactDetails
GROUP BY
    PersonID

Reference:

• Using PIVOT and UNPIVOT

How to use string type column in SQL pivot

If your dbms is sql-server you can try to use SUM condition aggregate function in a CTE

then use CAST with coalesce to make it.

;WITH CTE AS(
    SELECT Year,Name,
           SUM(CASE WHEN Bonus LIKE '%[0-9]%' THEN  CAST(Bonus AS DECIMAL) ELSE 0 END) total,
           COUNT(CASE WHEN Bonus = 'No Bonus' THEN 1 END) cnt
    FROM T 
    GROUP BY Year,Name
)
SELECT Name,
       coalesce(MAX(CASE WHEN  Year = 2011 THEN CAST(total AS VARCHAR(50)) END),'No Bonus') '2011',
       coalesce(MAX(CASE WHEN  Year = 2012 THEN CAST(total AS VARCHAR(50)) END),'No Bonus') '2012'
FROM CTE
GROUP BY Name

sqlfiddle

If you want to create columns dynamically you can try to use dynamic PIVOT.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

;WITH CTE AS(
    SELECT Year,Name,
           SUM(CASE WHEN Bonus LIKE '%[0-9]%' THEN  CAST(Bonus AS DECIMAL) ELSE 0 END) total,
           COUNT(CASE WHEN Bonus = 'No Bonus' THEN 1 END) cnt
    FROM T 
    GROUP BY Year,Name
)
SELECT  @cols = STUFF((SELECT distinct ',coalesce(MAX(CASE WHEN cnt > 0 and Year = ' + cast(Year as varchar(5)) + ' THEN ''No Bonus''  WHEN Year = ' + cast(Year as varchar(5)) + ' and cnt = 0 THEN CAST(total AS VARCHAR(50)) END),''0'')' + QUOTENAME(Year)
            FROM CTE c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
;WITH CTE AS(
    SELECT Year,Name,
           SUM(CASE WHEN Bonus LIKE ''%[0-9]%'' THEN  CAST(Bonus AS DECIMAL) ELSE 0 END) total,
           COUNT(CASE WHEN Bonus = ''No Bonus'' THEN 1 END) cnt
    FROM T 
    GROUP BY Year,Name
)
SELECT Name, ' + @cols + ' 
from CTE
GROUP BY Name'

exec(@query) 

sqlfiddle

SQL Server pivot with string concatenation

I have concatenated the answer list first per name and subject, then applied pivoting -

declare @temp table (name varchar(100), subject varchar(100), answer varchar(100))

insert into @temp
            select 'Pranesh','Physics'  ,'Numerical Problems'
union all   select 'Pranesh','Physics'  ,'Other'
union all   select 'Pranesh','Chemistry','Understanding Concepts'
union all   select 'Pranesh','Chemistry','Organic chemistry reactions'
union all   select 'Pranesh','Maths'    ,'Lack of understanding'
union all   select 'Pranesh','Maths'    ,'Insufficient practice'
union all   select 'Pranesh','Maths'    ,'Other'
union all   select 'Ramesh','Biology'   ,   'Other'
union all   select 'Ramesh','Biology'   ,   'Science'

;with cte as (select distinct name, subject from @temp)
select * from
(
    select
        c.name,
        c.subject,
        answer = stuff((select ',' + answer from @temp t where t.name=c.name and t.subject=c.subject for xml path('')), 1, 1, '')
    from cte c
) src
pivot
(
    max(answer) for subject in ([Physics], [Chemistry], [Maths], [Biology],[ComputerScience],[CommonUnderstanding])
) piv

Pivot String Values in Snowflake

The aggregating function can be max(). For example:

select * 
from (
    select xx.seq, xx.value:"@id" id, xx.value:"$" title
    from BooksXML, table(flatten(xml:"$":"$")) xx
)
pivot(max(title) for id in ('bk101', 'bk102', 'bk103', 'bk104', 'bk105')) as p
order by seq

With the table:

CREATE temp TABLE BooksXML
as
select parse_xml('<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book</book>
    <book id="bk102">The OK Book</book>
    <book id="bk103">The NOT Ok Book</book>
    <book id="bk104">All OK Book</book>
    <book id="bk105">Every OK Book</book>
  </Books>
</catalog>') xml
union all select parse_xml('
<catalog issue="spring">
  <Books>
    <book id="bk102">The OK Book1</book>
    <book id="bk103">The NOT Ok Book1</book>
    <book id="bk104">All OK Book1</book>
  </Books>
</catalog>')
union all select parse_xml('
<catalog issue="spring">
  <Books>
    <book id="bk101">The Good Book2</book>
    <book id="bk103">The NOT Ok Book2</book>
    <book id="bk104">All OK Book2</book>
    <book id="bk105">Every OK Book2</book>
  </Books>
</catalog>');

---

Related Topics