Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transposing multiple values while grouping and aggregating..possible? Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 2:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 2:07 PM
Points: 5, Visits: 8
Example...

Table 1 Columns
OriginCode | OCID
Loc1, A70
Loc2, A80
Loc3, A90

Table 2 Columns
DestID | OCID | Total
1000, A70, 14
1001, A80, 45
1002, A90, 57
1000, A90, 23

Output I'd like:

OriginCode | DestID | sumTotal
Loc1, Loc3 | 1000 | 37
Loc2| 1001 | 45
Loc3| 1002 | 57


So, as you can see, there can be multiple origin points for each DestID and rather than having two lines, I'd love to have one line.

Original code that gives multiples lines for DestID:

select a.origincode
, b.destid
, sum(total) as Total
from table1 a
join table2 b on a.ocid = b.ocid

group
by a.origincode
, b.destid
Post #1485993
Posted Monday, August 19, 2013 2:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 12,928, Visits: 12,346
Do you want the multiple values shoved into a single column? You can use STUFF to do that. If you could post up some ddl and sample data as create table and insert statements I can help you with it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1486006
Posted Monday, August 19, 2013 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 2:07 PM
Points: 5, Visits: 8
Yes, I'd like the OriginID in one column separated by a comma(if there are multiple values for that particular DestID).

There may be only one value but there again, it could be up to 3. I really want one line to keep the grand total in tact but have the ability to look over in the other column, see if it had multiple OriginID's and if so which they were.

I'll see if I can get the sample data together in the morning.

Thanks!
Post #1486008
Posted Monday, August 19, 2013 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 2:07 PM
Points: 5, Visits: 8
Got it done faster than I thought. So in this example, richmond should have one line but have three origincodes separated by comma. Kiosk should have two origincodes separated by commas and individual is only one origincode.

CREATE TABLE [dbo].[LOC](
[OriginCode] [varchar](40) NOT NULL,
[OCID] [varchar](8) NOT NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DEST](
[DestID] [varchar](40) NOT NULL,
[OCID] [varchar](8) NOT NULL,
[Ordered] [decimal](8,2) NULL,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[LOC](OriginCode,OCID)
SELECT 'Richmond', '804'
UNION ALL
SELECT 'Raleigh', '919'
UNION ALL
SELECT 'Roanoke', '540'
UNION ALL
SELECT 'Virginia Beach', '757'


INSERT INTO [dbo].[DEST](DestID,OCID,Ordered)
SELECT 'Business', '804', 8
UNION ALL
SELECT 'Individual', '919', 18
UNION ALL
SELECT 'Kiosk', '540', 64
UNION ALL
SELECT 'Business', '757', 12
UNION ALL
SELECT 'Business', '919', 36
UNION ALL
SELECT 'Kiosk', '757', 41


select origincode, destid, sum(ordered) as Total
from loc l join dest d on l.ocid = d.ocid
group
by origincode, destid
order
by destid



I'd like to see output like this in three colums(OriginCode,DestID,Total)

Raleigh, Richmond, Virginia Beach | Business | 56
Roanoke, Virginia Beach | Kiosk | 105
Raleigh | Individual | 18
Post #1486018
Posted Monday, August 19, 2013 7:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
Perhaps this works for you?

WITH JoinedTables AS (
SELECT a.OriginCode, a.OCID, b.DestID, b.Ordered
FROM LOC a
JOIN DEST b ON a.OCID = b.OCID)
SELECT OriginCode, DestID, Total=SUM(Ordered)
FROM (
SELECT OriginCode=STUFF((
SELECT ',' + OriginCode
FROM JoinedTables b
WHERE a.DestID = b.DestID
ORDER BY OriginCode
FOR XML PATH('')), 1, 1, '')
,DestID, Ordered
FROM JoinedTables a) a
GROUP BY OriginCode, DestID;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1486085
Posted Tuesday, August 20, 2013 6:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 2:07 PM
Points: 5, Visits: 8
Yes, Thanks!! Now I just need to try to wrap my ahead around understanding it and using it in the future.
Post #1486214
Posted Tuesday, August 20, 2013 10:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
jay parker-305162 (8/20/2013)
Yes, Thanks!! Now I just need to try to wrap my ahead around understanding it and using it in the future.


You're welcome!

Articles abound on the FOR XML PATH method to concatenate strings. Here's a SQL Spackle article by Wayne Sheffield called Creating a comma-separated list.

Because I didn't anticipate any special characters in the concatenated data that might send the XML creation awry, I omitted this part of the query from his example:

, TYPE).value('.','varchar(max)')




My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1486503
Posted Wednesday, August 21, 2013 2:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 2:07 PM
Points: 5, Visits: 8
Nice, I had actually hit that problem with some '&' symbols but the additional code fixed it
Post #1486924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse