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
 seperate into columns (grouping)

Author  Topic 

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 09:26:00
Hi,

I have query output as follows:

PackSlip Weight Item
001 12 1
001 13 2
002 12 3
002 13 4
002 14 5

I would like to get output that is separated into separate columns and so I can sum data, for example:

PackSlip1 Weight1 PackSlip2 Weight2
001 25 002 39

Right now I left outer join to get all this detail but how can I get this data on one row (grouping). I have done this before but am drawing a blank. If you need my query please ask but some pseudo would do, or if you need any more info, please let me know.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-04-23 : 09:34:54
how many columns can it span?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 09:35:36
quote:
Originally posted by visakh16

how many columns can it span?

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


Max 2 for now.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-04-23 : 09:37:45
Can you explain more clear why do you need one row?
To sum data isn't easier if you have one row with many different columns.
Hm...

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-04-23 : 09:39:24
[code]SELECT MAX(CASE WHEN rnk=1 THEN PackSlip ELSE NULL END) AS PackSlip1,
MAX(CASE WHEN rnk=1 THEN Weight ELSE NULL END) AS Weight1,
MAX(CASE WHEN rnk=2 THEN PackSlip ELSE NULL END) AS PackSlip2,
MAX(CASE WHEN rnk=2 THEN Weight ELSE NULL END) AS Weight2
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY PackSlip) AS Rnk,PackSlip,SUM(Weight) AS Weight
FROM Table
GROUP BY PackSlip
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 09:43:03
quote:
Originally posted by visakh16

SELECT MAX(CASE WHEN rnk=1 THEN PackSlip ELSE NULL END) AS PackSlip1,
MAX(CASE WHEN rnk=1 THEN Weight ELSE NULL END) AS Weight1,
MAX(CASE WHEN rnk=2 THEN PackSlip ELSE NULL END) AS PackSlip2,
MAX(CASE WHEN rnk=2 THEN Weight ELSE NULL END) AS Weight2
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY PackSlip) AS Rnk,PackSlip,SUM(Weight) AS Weight
FROM Table
GROUP BY PackSlip
)t


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


DENSE_RANK() ???

Yikes, okay thanks again Viskah, I'll give it a shot and let you know.
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 09:44:02
quote:
Originally posted by webfred

Can you explain more clear why do you need one row?
To sum data isn't easier if you have one row with many different columns.
Hm...

No, you're never too old to Yak'n'Roll if you're too young to die.

Requirement, we have one sales order with potentially two packing slips and we don't want to see detail. If there is no packslip2, then we leave the column data blank.... Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-04-23 : 09:44:52
quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

SELECT MAX(CASE WHEN rnk=1 THEN PackSlip ELSE NULL END) AS PackSlip1,
MAX(CASE WHEN rnk=1 THEN Weight ELSE NULL END) AS Weight1,
MAX(CASE WHEN rnk=2 THEN PackSlip ELSE NULL END) AS PackSlip2,
MAX(CASE WHEN rnk=2 THEN Weight ELSE NULL END) AS Weight2
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY PackSlip) AS Rnk,PackSlip,SUM(Weight) AS Weight
FROM Table
GROUP BY PackSlip
)t


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


DENSE_RANK() ???

Yikes, okay thanks again Viskah, I'll give it a shot and let you know.

yup unless you're on SQL 2000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 09:48:40
quote:
Originally posted by visakh16

SELECT MAX(CASE WHEN rnk=1 THEN PackSlip ELSE NULL END) AS PackSlip1,
MAX(CASE WHEN rnk=1 THEN Weight ELSE NULL END) AS Weight1,
MAX(CASE WHEN rnk=2 THEN PackSlip ELSE NULL END) AS PackSlip2,
MAX(CASE WHEN rnk=2 THEN Weight ELSE NULL END) AS Weight2
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY PackSlip) AS Rnk,PackSlip,SUM(Weight) AS Weight
FROM Table
GROUP BY PackSlip
)t


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



Vis, is there anyway to do this in the report design layout of an SSRS report instead of trying to mix this dense_rank function within my underlying query???
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-04-23 : 09:55:39
use a matrix container. Try applying grouping based on packslip and then apply rownumber over grouping and use it a column group expression.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 10:05:24
quote:
Originally posted by visakh16

use a matrix container. Try applying grouping based on packslip and then apply rownumber over grouping and use it a column group expression.

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


Sort of like dynamic column grouping?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-04-23 : 10:07:47
yup. rownumber will generate the values based on grouped field (packslip)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 10:11:53
quote:
Originally posted by visakh16

yup. rownumber will generate the values based on grouped field (packslip)

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


Ugh, sounds like a headache. Alright, I'll search around for some more info on column groups. Thanks again.

PS: If you have any let me know please (maybe like how to add one in the first place!), you have been more than helpful, thanks again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-04-23 : 10:13:58
I prefer doing this at query level though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SQLSoaker
Posting Yak Master

169 Posts
Posted - 2010-04-23 : 10:17:57
quote:
Originally posted by visakh16

I prefer doing this at query level though

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


Problem for me, is its getting a little tricky trying to add that function to this mess (AX DB):


SELECT SALESTABLE.SALESID, SALESLINE.ITEMID, SALESTABLE.CUSTACCOUNT, SALESTABLE.SALESNAME,
SALESTABLE.CREATEDDATETIME, SALESLINE.SHIPPINGDATEREQUESTED,
 CUSTPACKINGSLIPTRANS.DELIVERYDATE, INVENTTABLE.ITEMNAME, DLVMODE.TXT, DLVTERM.TXT AS TERMTXT,
(salesline.qtyordered * (inventtable.netweight + inventtable.taraweight)) as grossweight,
(DATEDIFF(dd, SALESLINE.SHIPPINGDATEREQUESTED, CUSTPACKINGSLIPTRANS.DELIVERYDATE) - (2 * DATEDIFF(wk,SALESLINE.SHIPPINGDATEREQUESTED, CUSTPACKINGSLIPTRANS.DELIVERYDATE))) AS WEEKDAYS, CUSTPACKINGSLIPTRANS.PACKINGSLIPID

from SALESTABLE


INNER JOIN SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID AND
						SALESTABLE.DATAAREAID = SALESLINE.DATAAREAID
						
LEFT OUTER JOIN CUSTPACKINGSLIPTRANS ON SALESLINE.INVENTTRANSID = CUSTPACKINGSLIPTRANS.INVENTTRANSID
					AND SALESLINE.DATAAREAID = CUSTPACKINGSLIPTRANS.DATAAREAID


INNER JOIN INVENTTABLE ON SALESLINE.ITEMID = INVENTTABLE.ITEMID 
				AND SALESLINE.DATAAREAID = INVENTTABLE.DATAAREAID

LEFT OUTER JOIN DLVMODE ON SALESLINE.DLVMODE = DLVMODE.CODE
LEFT OUTER JOIN DLVTERM ON SALESTABLE.DLVTERM = DLVTERM.CODE
					
INNER JOIN INVENTDIM ON SALESLINE.INVENTDIMID = INVENTDIM.INVENTDIMID				
WHERE SALESTABLE.DATAAREAID = @Company AND SALESLINE.SHIPPINGDATEREQUESTED >= @StartShip AND SALESLINE.SHIPPINGDATEREQUESTED <= @EndShip AND INVENTDIM.INVENTLOCATIONID IN (@LocID) AND INVENTTABLE.ITEMGROUPID IN (@ItemGroup) AND SALESTABLE.SALESTYPE IN (@SalesType)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site