SQL Server - multiple rows into one

Question

Hope you can help... I have data table in this format (Lets refer this table as 'Product')

productid   property_name   property_value  last_updated
p0001           type        p1              05-Oct-2010
p0001           name        Premium         05-Oct-2010
p0001           cost        172.00          05-Oct-2010
p0002           type        p3              06-Oct-2010
p0002           name        standard        06-Oct-2010
p0002           cost        13.00           06-Oct-2010

*(there are like 50 more properties of which i would need 15 atleast in my query. 
However, i just ignore them for this example)*

I would need the data in this format:

productid       type        name            cost
p0001           p1          Premium     172.00
p0002           p3          standard    13.00

I tried with a function and a view to get this format but it takes good few mins to get some 1000 records. Wonder if anyone knows quicker way?

What I tried:

Create function fun1(@productid nvarchar(50)) returns @retdetails table
(
type nvarchar(50) null,
name nvarchar(50) null,
cost nvarchar(50) null,
)
begin
declare
    @type nvarchar(50),
    @name nvarchar(50),
    @cost nvarchar(50),

    select @type=property_value from product where productid=@productid and property_name='type';
    select @name=property_value from product where productid=@productid and property_name='name';
    select @cost=property_value from product where productid=@productid and property_name='cost';

    if isnull(@productid,'')<>''
    begin
        insert @retdetails
            select @type, @name, @cost;
    end;
    return;
end;

then a view

select p.productid, pd.type, pd.name, pd.cost
from (select distinct productid from product) p
cross apply dbo.fun1(p.productid) pd

The slower response might be down to 'distinct' but without that I get duplicate records. I would appreciate any suggestion to get a quickier sql response.

Many Thanks

Thanks all for your quick responses. I dont own the table and its refreshed by some external object, so cant change the table (dont know why it was designed at first place, may be to make it easy to add new properties). tried self join, toooo long for 15 properties. Thanks Bogdan, I went to easier option (I dont understand pivot fully:))) — user981785, Oct 06 '11 at 09:47

score 1 · Accepted Answer · answered Oct 06 '11 at 09:13

1

You could try this PIVOT approach

SELECT productid,
       MAX(CASE WHEN property_name = 'type' THEN property_value END) AS type,
       MAX(CASE WHEN property_name = 'name' THEN property_value END) AS name,
       MAX(CASE WHEN property_name = 'cost' THEN property_value END) AS cost
FROM Product
GROUP BY productid

answered Oct 06 '11 at 09:13

Martin Smith

438,706
87
741
845

Many thanks Martin. Your query worked like a gem. finished in less than 4 secs retriving all 15 properties & some 1000 records. I wasted few hours on this, you made it simple :) – user981785 Oct 06 '11 at 09:46

score 0 · Answer 2 · answered Oct 06 '11 at 09:18

0

You could probably do this by joining the table back onto itself a couple of times, might be a bit of a performance issue though.

answered Oct 06 '11 at 09:18

Purplegoldfish

5,268
9
39
59

Would need to be 15 times if you look at the question closely but [might actually be more efficient than `PIVOT` depending on indexes.](http://stackoverflow.com/questions/7448453/sql-server-pivot-vs-multiple-join/7449213#7449213) – Martin Smith Oct 06 '11 at 09:21
Ah I skimmed that part, thought he had said 50 more rows not properties but he wold only need 15! – Purplegoldfish Oct 06 '11 at 09:25

score 0 · Answer 3 · answered Oct 06 '11 at 09:34

Starting from SQL Server 2005, you can use PIVOT operator:

DECLARE @TestData TABLE
(
     productid      VARCHAR(5) NOT NULL
    ,property_name  VARCHAR(5) NOT NULL 
    ,property_value VARCHAR(10)NOT NULL 
    ,last_updated   DATETIME NOT NULL
);
INSERT  @TestData 
SELECT 'p0001','type','p1'      ,'05-Oct-2010'
UNION ALL
SELECT 'p0001','name','Premium' ,'05-Oct-2010'
UNION ALL
SELECT 'p0001','cost','172.00'  ,'05-Oct-2010'
UNION ALL
SELECT 'p0002','type','p3'      ,'06-Oct-2010'
UNION ALL
SELECT 'p0002','name','standard','06-Oct-2010'
UNION ALL
SELECT 'p0002','cost','13.00'   ,'06-Oct-2010';

;WITH PivotSource
AS
(
    SELECT   a.productid
            ,a.property_name
            ,a.property_value 
    FROM    @TestData a
)
SELECT  pvt.*
        --,CONVERT(NUMERIC(8,2), pvt.cost) NumericCost
FROM    PivotSource src
PIVOT   ( MAX(src.property_value) FOR src.property_name IN ([type], [name], [cost]) ) pvt

Results:

productid   type    name    cost    NumericCost
p0001   p1  Premium 172.00  172.00
p0002   p3  standard13.00   13.00

SQL Server - multiple rows into one

3 Answers3