How return multiple row while pivoting table in sqlserver

Question

This is my query below in mssql

declare @cars as table (
 owners tinyint,
 attribute varchar(20),
 value varchar(20)
)
insert into @cars(owners, attribute, value)
values      (1, 'Make', 'VW'),
            (1, 'Model', 'Rabbit'),
            (1, 'Color', 'Gold'),
            (1, 'Make', 'V'),
            (1, 'Model', 'Rabbi'),
            (1, 'Color', 'Goldddd'),
            (2, 'Make', 'Jeep'),
            (2, 'Model', 'Wrangler'),
            (2, 'Color', 'Gray')


            select * from @cars


  select pvt.owners, pvt.Make, pvt.Model, pvt.Color
from @cars c
pivot (
 min(value)
 for attribute in ([Make],[Model],[Color])
) pvt

Above returns

Owners make model color

  1     v    rabbi gold

  2     jeep wrangler gray

but i need to return like

Owners make model color

  1     v    rabbi gold
  1     vw   rabbit golddd
  2     jeep wrangler gray

 how is possible?

why min(value) ? if you want all, use max(value)

makciook
– makciook

2013-09-30 12:13:19 +00:00
Commented Sep 30, 2013 at 12:13 — makciook
– makciook, Commented Sep 30, 2013 at 12:13
why have you joined VW with Goldddd instead of Gold?

roman
– roman

2013-09-30 12:23:57 +00:00
Commented Sep 30, 2013 at 12:23 — roman
– roman, Commented Sep 30, 2013 at 12:23

GarethD · Accepted Answer · 2013-09-30 12:27:42Z

You need to add an additional field to your data that gives each owners/attribute combination a unique value:

SELECT  pvt.owners, pvt.Make, pvt.Model, pvt.Color
FROM    (   SELECT  Owners, 
                    Attribute, 
                    Value, 
                    RowNum = ROW_NUMBER() OVER(PARTITION BY Owners, Attribute ORDER BY Value)
            FROM    @Cars
        ) c
        PIVOT
        (   MIN(Value)
            FOR Attribute IN ([Make], [Model], [Color])
        ) pvt
ORDER BY Owners, Make, Model, Color;

The key here is rather than just passing @cars to the pivot, the subquery adds an additional column to make the table being pivoted this:

Owners  Attribute   Value   RowNum
1       Color       Gold        1
1       Color       Goldddd     2
1       Make        V           1
1       Make        VW          2
1       Model       Rabbi       1
1       Model       Rabbit      2
2       Color       Gray        1
2       Make        Jeep        1
2       Model       Wrangler    1

So, that now there is an additional column to differentiate Gold from Goldddd for example, forcing them onto different rows in the pivot output.

You will run into problems though if there isn't a duplicate for all attributes, where there is missing data you will end up with NULL, e.g if you were to remove the values (1, 'Color', 'Goldddd') from your data, then the output would be:

owners  Make    Model       Color
1       V       Rabbi       Gold
1       VW      Rabbit      NULL
2       Jeep    Wrangler    Gray

Example on SQL Fiddle

Collectives™ on Stack Overflow

How return multiple row while pivoting table in sqlserver

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related