August 19, 2013 at 2:09 pm
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
August 19, 2013 at 2:31 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2013 at 2:37 pm
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!
August 19, 2013 at 2:56 pm
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
selectorigincode, destid, sum(ordered) as Total
fromloc l join dest d on l.ocid = d.ocid
group
byorigincode, destid
order
bydestid
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
August 19, 2013 at 7:32 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 20, 2013 at 6:16 am
Yes, Thanks!! Now I just need to try to wrap my ahead around understanding it and using it in the future.
August 20, 2013 at 10:12 pm
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[/url].
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 21, 2013 at 2:14 pm
Nice, I had actually hit that problem with some '&' symbols but the additional code fixed it 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply