April 5, 2018 at 4:09 am
hi,
my SQL knowledge got a little bit rusty. Trying with Group By failed, so this is my question:
CREATE TABLE #tblTemp1 (ID int, charge int)
INSERT INTO #tblTemp1 (ID, charge)
VALUES (1000, 300)
, (2500, 700)
, (3000, 900)
, (3000, 600)
, (4000, 300)
SELECT * FROM #tblTemp1
-- RESULT
ID charge
1000 300
2500 700
3000 900
3000 600
4000 300
-- I tried a little bit with coalesce
SELECT ID, Coalesce(CAST(charge AS nvarchar(100)) + ', ', '') AS Chargen
FROM #tblTemp1
--RESULT
ID Chargen
1000 300,
2500 700,
3000 900,
3000 600,
4000 300,
IF OBJECT_ID('tempdb..#tblTemp1') IS NOT NULL
DROP TABLE #tblTemp1
What I need is this:
--RESULT
ID Chargen
1000 300
2500 700
3000 900, 600
4000 300
any hints?
--
candide
________Panta rhei
April 5, 2018 at 5:20 am
What an excellent job posting ddl and sample data!!! I wish everyone would do that as well.
You can use STUFF and FOR XML to generate delimited list.
SELECT ID
, Chargen = Stuff((select ',' + convert(varchar(10), charge)
FROM #tblTemp1 t2
where t2.ID = t.ID
order by t2.charge
for xml path('')), 1, 1, '')
from #tblTemp1 t
group by t.ID
_______________________________________________________________
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/
April 5, 2018 at 5:30 am
Two suggestions, one is slightly more efficient as the sort is on a narrower set
😎
USE TEEST;
GO
SET NOCOUNT ON;
------------------------------------------
-- METHOD 1
------------------------------------------
IF OBJECT_ID(N'tempdb..#tblTemp1') IS NOT NULL DROP TABLE #tblTemp1;
CREATE TABLE #tblTemp1 (ID int, charge int) ;
INSERT INTO #tblTemp1 (ID, charge)
VALUES (1000, 300)
, (2500, 700)
, (3000, 900)
, (3000, 600)
, (4000, 300);
SELECT DISTINCT
SD.ID
,STUFF((
SELECT
CONCAT(',', SSD.charge)
FROM #tblTemp1 SSD
WHERE SD.ID = SSD.ID
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(50)'),1,1,'') AS charge
FROM #tblTemp1 SD;
------------------------------------------
-- METHOD 2
-- SLIGHTLY MORE EFFICIENT BECAUSE THE SORT
-- IS ON A NARROWER SET
------------------------------------------
;WITH DISTINCT_GROUPS AS
(
SELECT DISTINCT
T.ID
FROM #tblTemp1 T
)
SELECT
SD.ID
,STUFF((
SELECT
CONCAT(',', SSD.charge)
FROM #tblTemp1 SSD
WHERE SD.ID = SSD.ID
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(50)'),1,1,'') AS charge
FROM DISTINCT_GROUPS SD;
Output
ID charge
----- --------
1000 300
2500 700
3000 900,600
4000 300
April 5, 2018 at 5:38 am
Sean Lange - Thursday, April 5, 2018 5:20 AMWhat an excellent job posting ddl and sample data!!! I wish everyone would do that as well.You can use STUFF and FOR XML to generate delimited list.
SELECT ID
, Chargen = Stuff((select ',' + convert(varchar(10), charge)
FROM #tblTemp1 t2
where t2.ID = t.ID
order by t2.charge
for xml path('')), 1, 1, '')
from #tblTemp1 t
group by t.ID
Sean, this works but I have quick notes on the efficiency of this code:
😎
1. It requires two sort operations rather than one
2. Since the output of the XML clause is NVARCHAR, the sort operation for the output is majority of the total cost.
3. Any extended characters, even space (%20 in url encoding), will garble the output.
April 5, 2018 at 5:48 am
Eirikur Eiriksson - Thursday, April 5, 2018 5:38 AMSean Lange - Thursday, April 5, 2018 5:20 AMWhat an excellent job posting ddl and sample data!!! I wish everyone would do that as well.You can use STUFF and FOR XML to generate delimited list.
SELECT ID
, Chargen = Stuff((select ',' + convert(varchar(10), charge)
FROM #tblTemp1 t2
where t2.ID = t.ID
order by t2.charge
for xml path('')), 1, 1, '')
from #tblTemp1 t
group by t.IDSean, this works but I have quick notes on the efficiency of this code:
😎
1. It requires two sort operations rather than one
2. Since the output of the XML clause is NVARCHAR, the sort operation for the output is majority of the total cost.
3. Any extended characters, even space (%20 in url encoding), will garble the output.
Excellent stuff. Thanks.
The second sort is to ensure the order of the delimited values. If you remove the sort this method is actually a bit faster than the ones you posted.
SELECT ID
, Chargen = Stuff((select ',' + convert(varchar(10), charge)
FROM #tblTemp1 t2
where t2.ID = t.ID
--order by t2.charge
for xml path('')), 1, 1, '')
from #tblTemp1 t
group by t.ID
It does however still have issue of extended characters. I like using CONCAT here. Solves that issue quite nicely.
_______________________________________________________________
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/
April 5, 2018 at 5:49 am
Further on the previous post, if it is certain that there will only be two values for each ID at the most, this is probably the fastest solution, can be extended using ROW_NUMBER if there will be only handful of values for each ID. It is 3 to 4 times more efficient then the other solutions.
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#tblTemp1') IS NOT NULL DROP TABLE #tblTemp1;
CREATE TABLE #tblTemp1 (ID int, charge int) ;
INSERT INTO #tblTemp1 (ID, charge)
VALUES (1000, 300)
, (2500, 700)
, (3000, 900)
, (3000, 600)
, (4000, 300);
;WITH GROUP_VAL AS
(
SELECT
SD.ID
,MIN(SD.charge) AS VAL01
,MAX(SD.charge) AS VAL02
FROM #tblTemp1 SD
GROUP BY SD.ID
)
SELECT
GV.ID
,CONCAT(GV.VAL01
,CASE
WHEN GV.VAL01 <> GV.VAL02 THEN CONCAT(',',GV.VAL02)
ELSE ''
END) AS charge
FROM GROUP_VAL GV;
April 5, 2018 at 5:53 am
Sean Lange - Thursday, April 5, 2018 5:48 AMEirikur Eiriksson - Thursday, April 5, 2018 5:38 AMSean Lange - Thursday, April 5, 2018 5:20 AMWhat an excellent job posting ddl and sample data!!! I wish everyone would do that as well.You can use STUFF and FOR XML to generate delimited list.
SELECT ID
, Chargen = Stuff((select ',' + convert(varchar(10), charge)
FROM #tblTemp1 t2
where t2.ID = t.ID
order by t2.charge
for xml path('')), 1, 1, '')
from #tblTemp1 t
group by t.IDSean, this works but I have quick notes on the efficiency of this code:
😎
1. It requires two sort operations rather than one
2. Since the output of the XML clause is NVARCHAR, the sort operation for the output is majority of the total cost.
3. Any extended characters, even space (%20 in url encoding), will garble the output.Excellent stuff. Thanks.
The second sort is to ensure the order of the delimited values. If you remove the sort this method is actually a bit faster than the ones you posted.
SELECT ID
, Chargen = Stuff((select ',' + convert(varchar(10), charge)
FROM #tblTemp1 t2
where t2.ID = t.ID
--order by t2.charge
for xml path('')), 1, 1, '')
from #tblTemp1 t
group by t.ID
It does however still have issue of extended characters. I like using CONCAT here. Solves that issue quite nicely.
Perfect example of the cost of implying an non-existing order within a set 😉
😎
April 5, 2018 at 6:35 am
candide - Thursday, April 5, 2018 6:22 AMthanx for your answers, but this is a SS2008 forum and I use SS2008R2.:doze:
I tried your suggestions but CONCAT only works starting with SS2012
What' s the simplest workaround?
The solution I posted works just fine in 2008.
_______________________________________________________________
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/
April 5, 2018 at 6:44 am
candide - Thursday, April 5, 2018 6:22 AMthanx for your answers, but this is a SS2008 forum and I use SS2008R2.:doze:
I tried your suggestions but CONCAT only works starting with SS2012
What' s the simplest workaround?
Just change the concat to the concatenation operator (+), absolutely trivial.
😎
My question is, why are you using an unsupported, 10 year old version?
April 5, 2018 at 6:48 am
but I get "Message 195" for the Concat-line in SS2008R2, so I changed Method1 slightly
SELECT DISTINCT
SD.ID
,STUFF((
SELECT
--CONCAT(',', SSD.charge)
{fn CONCAT(', ', CAST(SSD.charge AS nvarchar(50))) }
FROM #tblTemp1 SSD
WHERE SD.ID = SSD.ID
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(50)'),1,1,'') AS charge
FROM #tblTemp1 SD;
-- RESULT
ID charge
1000 300
2500 700
3000 900, 600
4000 300
q.e.d.
thanks to all forum contributors, you are great
--
candide
________Panta rhei
April 5, 2018 at 6:50 am
candide - Thursday, April 5, 2018 6:22 AMMy question is, why are you using an unsupported, 10 year old version?
I have some customers still using SS2008R2 and I see no chance they upgrade until Microsoft pulls the plug:laugh:
--
candide
________Panta rhei
April 5, 2018 at 7:01 am
Eirikur Eiriksson - Thursday, April 5, 2018 6:44 AMcandide - Thursday, April 5, 2018 6:22 AMMy question is, why are you using an unsupported, 10 year old version?
I have some customers still using SS2008R2 and I see no chance they upgrade until Microsoft pulls the plug:laugh:
You need to tell then that they are sitting in an empty bath tub, M$ doesn't notify each individual user of when they pull the plug😉
😎
April 5, 2018 at 7:24 am
Eirikur Eiriksson - Thursday, April 5, 2018 7:01 AMYou need to tell then that they are sitting in an empty bath tub, M$ doesn't notify each individual user of when they pull the plug😉
😎
well everytime I see the DB Admins I talk with them about this fact, counting the seconds how long it takes until they are rolling their eyes:smooooth:
So I don't care anymore about the companies update decisions... It's alone in their responsibility
--
candide
________Panta rhei
April 5, 2018 at 7:27 am
candide - Thursday, April 5, 2018 6:50 AMEirikur Eiriksson - Thursday, April 5, 2018 6:44 AMcandide - Thursday, April 5, 2018 6:22 AMMy question is, why are you using an unsupported, 10 year old version?
I have some customers still using SS2008R2 and I see no chance they upgrade until Microsoft pulls the plug:laugh:
You need to tell then that they are sitting in an empty bath tub, M$ doesn't notify each individual user of when they pull the plug😉
😎
To that end it isn't like the database will just suddenly stop working. Honestly the plug was pulled already on 2008/R2. Nonetheless I know what you mean about people not being willing to upgrade. My company still has a 2005 instance in production. Thankfully only one and it is only used by a couple of ancient applications that are near end of life themselves. By the end of this year (or sooner if I can make it happen) they will be gone.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply