CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Proxx
Proxx

Reputation: 151

sql query to get multiple records in one row

Hi im working on a query to get 1row for each product but the problem is that there are multiple records with propertys of 1 product.

TABLE PRODUCTS_DATA

NAME :  DESC.  | ID|FIELD1|FIELD2|FIELD3|FIELD4|
Row 1:PRODUCT1 | 1 | 200  | high |      |      | 
Row 2:PRODUCT1 | 2 |  10  |      | low  |      |
Row 3:PRODUCT1 | 3 |      |      |      | 10   |

now i would like the get a query which displays only one record with all those properties.

Row 1: PRODUCT1|200|10|HIGH|LOW|10

because 

row 1 | id1 | field 1 contains length data
row 2 | id2 | field 1 contains amount data

how in the world is is possible to get an result like this?

Upvotes: 0

Views: 4110

Answers (3)

kaushik0033
kaushik0033

Reputation: 677

I think you will get your solution now dear...

   SELECT 
   SS.[DESC], 
   (SELECT '|' + US.FIELD1
    FROM XYZ US
    WHERE US.[DESC] = SS.[DESC]
    FOR XML PATH('')) [FIELD1],
    (SELECT '|' + US.FIELD2
    FROM XYZ US
    WHERE US.[DESC] = SS.[DESC]
    FOR XML PATH('')) [FIELD2],
    (SELECT '|' + US.FIELD3
    FROM XYZ US
    WHERE US.[DESC] = SS.[DESC]
    FOR XML PATH('')) [FIELD3],
    (SELECT '|' + US.FIELD4
    FROM XYZ US
    WHERE US.[DESC] = SS.[DESC]
    FOR XML PATH('')) [FIELD4]
FROM XYZ SS
GROUP BY SS.[DESC]
ORDER BY 1

Upvotes: 1

jacouh
jacouh

Reputation: 8769

Please Try this, suppose that PRODUCTS_DATA.PRODUCT_DESC column gives the product description (DESC.):

SELECT PRODUCT_DESC,MAX(FIELD1),MAX(FIELD2),MAX(FIELD3),MAX(FIELD4) FROM PRODUCTS_DATA
GROUP BY PRODUCT_DESC;

Upvotes: 0

Dan
Dan

Reputation: 368

You need to be careful about the semantics of the two "field1" columns. You are showing both coming out in your example result. Presumably you could end up with data in each of the four fields for each row and therefore a very variable length result with duplicate column names, which seems broken to me. (Or you need to fix whatever is inserting the data to use a real primary key!)

Upvotes: 0

Related Questions