group multiple rows into single row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

group multiple rows into single row?

Author

Topic

katethegreat
Starting Member

13 Posts

Posted - 2012-05-17 : 13:26:56

Hi guys,

I have an export that gives me products that my store manager needs to order, but each product has several variations and so the export returns multiple rows for each variation. I need to get the product code and variations into one row. Here's the query that returns the info I need, but it's in multiple rows and I need it in a single row:

SELECT DISTINCT
COALESCE
( (CASE WHEN productcode LIKE '%4MM%' THEN (IsChildOfProductCode + '-' + '4MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%5MM%' THEN (IsChildOfProductCode + '-' + '5MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%6MM%' THEN (IsChildOfProductCode + '-' + '6MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%7MM%' THEN (IsChildOfProductCode + '-' + '7MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%8MM%' THEN (IsChildOfProductCode + '-' + '8MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%9MM%' THEN (IsChildOfProductCode + '-' + '9MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%10MM%' THEN (IsChildOfProductCode + '-' + '10MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%12MM%' THEN (IsChildOfProductCode + '-' + '12MM') ELSE NULL END)

)

As SKU

, CASE WHEN productcode LIKE '%SIZE-4' THEN StockReOrderQty ELSE NULL END As '4.0'
, CASE WHEN productcode LIKE '%SIZE-4.5' THEN StockReOrderQty ELSE NULL END As '4.5'
, CASE WHEN productcode LIKE '%SIZE-5' THEN StockReOrderQty ELSE NULL END As '5.0'
, CASE WHEN productcode LIKE '%SIZE-5.5' THEN StockReOrderQty ELSE NULL END As '5.5'
, CASE WHEN productcode LIKE '%SIZE-6' THEN StockReOrderQty ELSE NULL END As '6.0'
, CASE WHEN productcode LIKE '%SIZE-6.5' THEN StockReOrderQty ELSE NULL END As '6.5'
, CASE WHEN productcode LIKE '%SIZE-7' THEN StockReOrderQty ELSE NULL END As '7.0'
, CASE WHEN productcode LIKE '%SIZE-7.5' THEN StockReOrderQty ELSE NULL END As '7.5'
, CASE WHEN productcode LIKE '%SIZE-8' THEN StockReOrderQty ELSE NULL END As '8.0'
, CASE WHEN productcode LIKE '%SIZE-8.5' THEN StockReOrderQty ELSE NULL END As '8.5'
, CASE WHEN productcode LIKE '%SIZE-9' THEN StockReOrderQty ELSE NULL END As '9.0'
, CASE WHEN productcode LIKE '%SIZE-9.5' THEN StockReOrderQty ELSE NULL END As '9.5'
, CASE WHEN productcode LIKE '%SIZE-10' THEN StockReOrderQty ELSE NULL END As '10.0'
, CASE WHEN productcode LIKE '%SIZE-10.5' THEN StockReOrderQty ELSE NULL END As '10.5'
, CASE WHEN productcode LIKE '%SIZE-11' THEN StockReOrderQty ELSE NULL END As '11.0'
, CASE WHEN productcode LIKE '%SIZE-11.5' THEN StockReOrderQty ELSE NULL END As '11.5'
, CASE WHEN productcode LIKE '%SIZE-12' THEN StockReOrderQty ELSE NULL END As '12.0'
, CASE WHEN productcode LIKE '%SIZE-12.5' THEN StockReOrderQty ELSE NULL END As '12.5'
, CASE WHEN productcode LIKE '%SIZE-13' THEN StockReOrderQty ELSE NULL END As '13.0'
, CASE WHEN productcode LIKE '%SIZE-13.5' THEN StockReOrderQty ELSE NULL END As '13.5'
, CASE WHEN productcode LIKE '%SIZE-14' THEN StockReOrderQty ELSE NULL END As '14.0'
, CASE WHEN productcode LIKE '%SIZE-14.5' THEN StockReOrderQty ELSE NULL END As '14.5'
, CASE WHEN productcode LIKE '%SIZE-15' THEN StockReOrderQty ELSE NULL END As '15.0'
, CASE WHEN productcode LIKE '%SIZE-15.5' THEN StockReOrderQty ELSE NULL END As '15.5'
, CASE WHEN productcode LIKE '%SIZE-16' THEN StockReOrderQty ELSE NULL END As '16.0'
, CASE WHEN productcode LIKE '%SIZE-16.5' THEN StockReOrderQty ELSE NULL END As '16.5'

FROM Products_Joined
WHERE
IsChildOfProductCode IS NOT NULL
AND addtopo_now = 'Y'

;

which gives me this:

But I need it like this:

Any help would be most appreciated, I'm getting a bruise on my forehead from banging it against my monitor.

Thanks in advance,

Kate the Great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-19 : 14:33:30

[code]
SELECT
COALESCE
( (CASE WHEN productcode LIKE '%4MM%' THEN (IsChildOfProductCode + '-' + '4MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%5MM%' THEN (IsChildOfProductCode + '-' + '5MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%6MM%' THEN (IsChildOfProductCode + '-' + '6MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%7MM%' THEN (IsChildOfProductCode + '-' + '7MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%8MM%' THEN (IsChildOfProductCode + '-' + '8MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%9MM%' THEN (IsChildOfProductCode + '-' + '9MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%10MM%' THEN (IsChildOfProductCode + '-' + '10MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%12MM%' THEN (IsChildOfProductCode + '-' + '12MM') ELSE NULL END)

)

As SKU

, MAX(CASE WHEN productcode LIKE '%SIZE-4' THEN StockReOrderQty ELSE NULL END) As '4.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-4.5' THEN StockReOrderQty ELSE NULL END) As '4.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-5' THEN StockReOrderQty ELSE NULL END) As '5.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-5.5' THEN StockReOrderQty ELSE NULL END) As '5.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-6' THEN StockReOrderQty ELSE NULL END) As '6.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-6.5' THEN StockReOrderQty ELSE NULL END) As '6.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-7' THEN StockReOrderQty ELSE NULL END) As '7.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-7.5' THEN StockReOrderQty ELSE NULL END) As '7.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-8' THEN StockReOrderQty ELSE NULL END) As '8.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-8.5' THEN StockReOrderQty ELSE NULL END) As '8.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-9' THEN StockReOrderQty ELSE NULL END) As '9.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-9.5' THEN StockReOrderQty ELSE NULL END) As '9.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-10' THEN StockReOrderQty ELSE NULL END) As '10.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-10.5' THEN StockReOrderQty ELSE NULL END) As '10.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-11' THEN StockReOrderQty ELSE NULL END) As '11.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-11.5' THEN StockReOrderQty ELSE NULL END) As '11.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-12' THEN StockReOrderQty ELSE NULL END) As '12.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-12.5' THEN StockReOrderQty ELSE NULL END) As '12.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-13' THEN StockReOrderQty ELSE NULL END) As '13.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-13.5' THEN StockReOrderQty ELSE NULL END) As '13.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-14' THEN StockReOrderQty ELSE NULL END) As '14.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-14.5' THEN StockReOrderQty ELSE NULL END) As '14.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-15' THEN StockReOrderQty ELSE NULL END) As '15.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-15.5' THEN StockReOrderQty ELSE NULL END) As '15.5'
, MAX(CASE WHEN productcode LIKE '%SIZE-16' THEN StockReOrderQty ELSE NULL END) As '16.0'
, MAX(CASE WHEN productcode LIKE '%SIZE-16.5' THEN StockReOrderQty ELSE NULL END) As '16.5'

FROM Products_Joined
WHERE
IsChildOfProductCode IS NOT NULL
AND addtopo_now = 'Y'
GROUP BY COALESCE
( (CASE WHEN productcode LIKE '%4MM%' THEN (IsChildOfProductCode + '-' + '4MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%5MM%' THEN (IsChildOfProductCode + '-' + '5MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%6MM%' THEN (IsChildOfProductCode + '-' + '6MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%7MM%' THEN (IsChildOfProductCode + '-' + '7MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%8MM%' THEN (IsChildOfProductCode + '-' + '8MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%9MM%' THEN (IsChildOfProductCode + '-' + '9MM') ELSE NULL END)
,( CASE WHEN productcode LIKE '%10MM%' THEN (IsChildOfProductCode + '-' + '10MM') ELSE NULL END)
, (CASE WHEN productcode LIKE '%12MM%' THEN (IsChildOfProductCode + '-' + '12MM') ELSE NULL END)

)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

katethegreat
Starting Member

13 Posts

Posted - 2012-05-20 : 15:59:47

Worked perfectly, thanks so much!

Kate the Great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-20 : 17:30:26

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources