Pivot table for string in SQL Server

Question

I have the following table with some details as shown below:

Example:

Creating table product:

create table product
(
  slno int,
  item nvarchar(20)
);

Inserting some records:

insert into product values(1,'HDD');
insert into product values(2,'PenDrive');
insert into product values(3,'RAM');
insert into product values(4,'DVD');
insert into product values(5,'RAM');
insert into product values(6,'HDD');

Table product contains:

select * from product;

slno  item
---------- 
 1    HDD
 2    PenDrive
 3    RAM
 4    DVD
 5    RAM
 6    HDD

Now I want make a string of items for which i have written the following script:

select distinct 
(
   select distinct item+','
   from product
   FOR XML PATH('')
) temp
from product;

Result is:

 temp
----------------------
DVD,HDD,PenDrive,RAM,

Note: Now I want to show the result in the following format: (In which I need to use the pivot table with the above query and need to display how many product have sold out).

DVD  HDD  PenDrive  RAM
-----------------------
 1    2      1       2

My Try:

 select DVD,HDD,PenDrive,RAM
 from
 (
      select distinct 
      (
         select distinct item+','
         from product
         FOR XML PATH('')
      ) temp 
 from product
 ) as a
 pivot 
 (
    count(temp)
    for temp in(DVD,HDD,PenDrive,RAM)
 ) pt

But getting result :

DVD  HDD  PenDrive  RAM
------------------------
 0    0      0       0

@RafalZiolkowski, Plz check out my try.

Sarfaraz Makandar
– Sarfaraz Makandar

2014-08-22 11:11:33 +00:00
Commented Aug 22, 2014 at 11:11 — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Aug 22, 2014 at 11:11

Taryn · Accepted Answer · 2014-08-22 11:11:26Z

1

You don't need to use the FOR XML PATH to create a string of the columns unless you need a dynamic SQL version to get your final result.

Using PIVOT you can easily hard-code your values for your query:

select DVD, HDD, PenDrive, RAM
from
(
  select item
  from product
) d
pivot
(
  count(item)
  for item in (DVD, HDD, PenDrive, RAM)
) piv;

See SQL Fiddle with Demo.

Now if you had an unknown values that you needed to be the final columns, then you'd create a list of the items and execute a SQL string via dynamic SQL similar to:

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

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

set @query = 'SELECT ' + @cols + ' 
            from 
            (
                select item
                from product
            ) x
            pivot 
            (
                count(item)
                for item in (' + @cols + ')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo

answered Aug 22, 2014 at 11:11

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sarfaraz Makandar Over a year ago

Exactly, I need to run dynamic SQL query to get result.

Taryn Over a year ago

@Meem I thought so, so I already added a version that does that.

Sarfaraz Makandar Over a year ago

Yup! I got it. Thank you so much for your valuable assistance.

Collectives™ on Stack Overflow

Pivot table for string in SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related