October 13, 2014 at 8:35 am
I have come across this and I felt a little difficulty in doing this. please go through this once.
create table example
( a int null,
b varchar(100) null,
c varchar(100) null,
d int null)
insert into example
select 220,'abc','yes',1 union all
select 220,'abc','yes',2 union all
select 220,'abc','yes',3 union all
select 220,'abc','yes',4 union all
select 220,'abc','no',132 union all
select 220,'abc','no',23 union all
select 220,'abc','no',34 union all
select 220,'abc','no',43 union all
select 243,'raju','NA',123 union all
select 243,'raju','NA',456 union all
select 243,'raju','NA',789 union all
select 243,'raju','Reg',21 union all
select 243,'raju','Reg',23 union all
select 243,'raju','Reg',12
I want to display the result as
220,'abc', 'yes(1,2,3,4), no(132,23,34,43)'
243,'raju', 'NA(123,456,789), Reg(21,23,12)'
October 13, 2014 at 8:36 am
Can u please help me with this?
Thanks
Gautham
October 13, 2014 at 9:00 am
Happy to help, but this really looks like a homework issue, and we prefer to help you once you've tried something, rather than giving you some answer.
A few hints. First, you'll need to tackle this by getting the row values into a string. You can do something like:
DECLARE @response VARCHAR(8000)
SELECT @response = @response + ', ' + d
FROM example
Use that, with some concatenation to get the string of responses. Then you'll probably make that a CTE or subquery you join back to the regular table with some distinct other values.
October 13, 2014 at 9:10 am
I have come across this and I felt a little difficulty in doing this. please go through this once.
create table example
( a int null,
b varchar(100) null,
c varchar(100) null,
d varchar(1000) null)
insert into example
select 220,'abc','yes',1 union all
select 220,'abc','yes',2 union all
select 220,'abc','yes',3 union all
select 220,'abc','yes',4 union all
select 220,'abc','no',132 union all
select 220,'abc','no',23 union all
select 220,'abc','no',34 union all
select 220,'abc','no',43 union all
select 243,'raju','NA',123 union all
select 243,'raju','NA',456 union all
select 243,'raju','NA',789 union all
select 243,'raju','Reg',21 union all
select 243,'raju','Reg',23 union all
select 243,'raju','Reg',12
I want to display the result as
220,'abc', 'yes(1,2,3,4), no(132,23,34,43)'
243,'raju', 'NA(123,456,789), Reg(21,23,12)'
Please correct the datatype of column d as varchar(1000).
I also tried using the below query. but I m not getting the actual result.
select a,b, stuff(
(select ', '+lovs from
(select a,b,c,c+'('+
stuff((select ', '+d from example with (nolock)
where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')
as lovs from example as x with (nolock) group by a,b,c)
y
where a = y.a and b = y.b
for xml path('')),1,1,'')
from example group by a,b
October 13, 2014 at 9:12 am
What has become a classic method for doing this may be found at the following URL...
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2014 at 9:02 pm
It's late and I'm out of coffee so I might not be thinking clearly. This does 1 + x + y table scans where "x" is the
unique number of combinations of a/b and y is the number of unique values in in the "c" column, which is absolutely terrible. Someone with a better grip on FOR XML PATH might be able to pull something a little better off.
Still, the following code does work.
WITH
ctePreAgg AS
(
SELECT eo.a
,eo.b
--,c_Unique = eo.c --Don't need this anymore
,d_CSV = STUFF(
(
SELECT ','+CAST(ei.d AS VARCHAR(10))
FROM dbo.example ei
WHERE ei.a = eo.a
AND ei.b = eo.b
AND ei.c = eo.c
ORDER BY ei.d
FOR XML PATH('')
)
,1,1,eo.c+'(')
+ ')'
FROM dbo.Example eo
GROUP BY eo.a,eo.b,eo.c
)
SELECT pao.a
,pao.b
,Final_CSV = STUFF(
(
SELECT ','+pai.d_CSV
FROM ctePreAgg pai
WHERE pai.a = pao.a
AND pai.b = pao.b
ORDER BY pai.d_CSV
FOR XML PATH('')
)
,1,1,'')
FROM ctePreAgg pao
GROUP BY pao.a, pao.b
;
Using the cte code to populate a temp table instead of popping the cte twice cuts the reads to 1/3rd of the cte without doing much for cpu, but only if you add a clustered index on a,b,c. Like this...
SELECT eo.a
,eo.b
--,c_Unique = eo.c --Don't need this anymore
,d_CSV = STUFF(
(
SELECT ','+CAST(ei.d AS VARCHAR(10))
FROM dbo.example ei
WHERE ei.a = eo.a
AND ei.b = eo.b
AND ei.c = eo.c
ORDER BY ei.d
FOR XML PATH('')
)
,1,1,eo.c+'(')
+ ')'
INTO #PreAgg
FROM dbo.Example eo
GROUP BY eo.a,eo.b,eo.c
SELECT pao.a
,pao.b
,Final_CSV = STUFF(
(
SELECT ','+pai.d_CSV
FROM #PreAgg pai
WHERE pai.a = pao.a
AND pai.b = pao.b
ORDER BY pai.d_CSV
FOR XML PATH('')
)
,1,1,'')
FROM #PreAgg pao
GROUP BY pao.a, pao.b
;
Like I said, it's late and I hope someone else can come up with a better solution.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2014 at 10:24 pm
Quick solution and an opposite problem to Jeff's, lots of coffee but yet to be drunken.
😎
USE tempdb;
GO
IF (SELECT OBJECT_ID(N'dbo.example')) IS NOT NULL DROP TABLE dbo.example;
create table dbo.example
( a int null,
b varchar(100) null,
c varchar(100) null,
d varchar(1000) null);
insert into dbo.example
select 220,'abc','yes',1 union all
select 220,'abc','yes',2 union all
select 220,'abc','yes',3 union all
select 220,'abc','yes',4 union all
select 220,'abc','no',132 union all
select 220,'abc','no',23 union all
select 220,'abc','no',34 union all
select 220,'abc','no',43 union all
select 243,'raju','NA',123 union all
select 243,'raju','NA',456 union all
select 243,'raju','NA',789 union all
select 243,'raju','Reg',21 union all
select 243,'raju','Reg',23 union all
select 243,'raju','Reg',12 ;
;WITH VALUE_LIST AS
(
SELECT DISTINCT
E.a
,E.b
,E.c
,E.c + '(' + STUFF((SELECT ',' + EX.d
FROM dbo.example EX
WHERE E.a = EX.A
AND E.c = EX.C
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(MAX)'),1,1,'') + ')' AS PAARTS
FROM dbo.example E
)
SELECT DISTINCT
VL.a
,VL.b
,STUFF((SELECT ', ' + VX.PAARTS
FROM VALUE_LIST VX
WHERE VL.a = VX.a
FOR XML PATH(''), TYPE).value('.[1]','VARCHAR(MAX)'),1,2,'') AS CONCAT_VAL
FROM VALUE_LIST VL;
Results
a b CONCAT_VAL
----- ------ -------------------------------
220 abc no(132,23,34,43), yes(1,2,3,4)
243 raju NA(123,456,789), Reg(21,23,12)
October 14, 2014 at 9:50 am
Another quick solution, less flexibility but only a single table scan
😎
USE tempdb;
GO
IF (SELECT OBJECT_ID(N'dbo.example')) IS NOT NULL DROP TABLE dbo.example;
create table dbo.example
( a int null,
b varchar(100) null,
c varchar(100) null,
d varchar(1000) null);
insert into dbo.example
select 220,'abc','yes',1 union all
select 220,'abc','yes',2 union all
select 220,'abc','yes',3 union all
select 220,'abc','yes',4 union all
select 220,'abc','no',132 union all
select 220,'abc','no',23 union all
select 220,'abc','no',34 union all
select 220,'abc','no',43 union all
select 243,'raju','NA',123 union all
select 243,'raju','NA',456 union all
select 243,'raju','NA',789 union all
select 243,'raju','Reg',21 union all
select 243,'raju','Reg',23 union all
select 243,'raju','Reg',12 ;
;WITH BASE_DATA AS
(
SELECT
E.a
,E.b
,ROW_NUMBER() OVER
(
PARTITION BY E.a
,E.c
ORDER BY E.d
) AS E_RID
,E.c
,E.d
FROM dbo.example E
)
,CONCAT_BASE AS
(
SELECT
BD.a
,BD.b
,CONCAT(BD.c, '('
,ISNULL( MAX(CASE WHEN BD.E_RID = 1 THEN BD.d END),'')
,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 2 THEN BD.d END),'')
,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 3 THEN BD.d END),'')
,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 4 THEN BD.d END),'')
,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 5 THEN BD.d END),'')
,ISNULL(',' + MAX(CASE WHEN BD.E_RID = 6 THEN BD.d END),''),')') AS B_VAL
FROM BASE_DATA BD
GROUP BY BD.a
,BD.b
,BD.c
)
,GROUPED_VALUES AS
(
SELECT
CB.a
,CB.b
,ROW_NUMBER() OVER (PARTITION BY CB.a ORDER BY (SELECT NULL)) AS CB_RID
,CB.B_VAL
FROM CONCAT_BASE CB
)
SELECT
GV.a
,GV.b
,CONCAT
(
MAX(CASE WHEN GV.CB_RID = 1 THEN GV.B_VAL END)
,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 2 THEN GV.B_VAL END),'')
,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 3 THEN GV.B_VAL END),'')
,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 4 THEN GV.B_VAL END),'')
,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 5 THEN GV.B_VAL END),'')
,ISNULL(', ' + MAX(CASE WHEN GV.CB_RID = 6 THEN GV.B_VAL END),'')
) AS CONC_VAL
FROM GROUPED_VALUES GV
GROUP BY GV.a,GV.b;
Results
a b CONC_VAL
----- ------ -------------------------------
220 abc no(132,23,34,43), yes(1,2,3,4)
243 raju NA(123,456,789), Reg(12,21,23)
October 14, 2014 at 10:47 pm
Thanks everyone.
But Can we achieve the result without cte.
I want to use stuff inside stuff.
And I achieved it guys.
Thanks for supporting me guys..
October 14, 2014 at 11:26 pm
gautham.gn (10/14/2014)
Thanks everyone.But Can we achieve the result without cte.
I want to use stuff inside stuff.
And I achieved it guys.
Thanks for supporting me guys..
Great. Glad you achieved what you wanted. But it's a two way street here. Please post your code so that we can learn what you did to avoid CTEs for this problem and the reason why you wanted to avoid CTEs to begin with. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2014 at 12:46 am
Hi Guys,
This is how I achieved the result.
select a,b, stuff(
(select ', '+level1 from
(select a,b,c+'('+
stuff((select ', '+d from example with (nolock)
where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')
as level1 from example as x with (nolock) group by a,b,c) y
where a = z.a and b = z.b
for xml path('')),1,1,'') as level2
from
(select a,b from example with (nolock) group by a,b)z
October 15, 2014 at 12:58 am
Quick question, why are you using the nolock hint?
😎
October 15, 2014 at 8:36 am
Don't use NOLOCK
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
October 15, 2014 at 9:19 am
gautham.gn (10/15/2014)
Hi Guys,This is how I achieved the result.
select a,b, stuff(
(select ', '+level1 from
(select a,b,c+'('+
stuff((select ', '+d from example with (nolock)
where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')
as level1 from example as x with (nolock) group by a,b,c) y
where a = z.a and b = z.b
for xml path('')),1,1,'') as level2
from
(select a,b from example with (nolock) group by a,b)z
Thanks for posting that though I'm still curious why you considered it to be important to avoid CTEs.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2015 at 7:09 pm
gautham.gn (10/15/2014)
Hi Guys,This is how I achieved the result.
select a,b, stuff(
(select ', '+level1 from
(select a,b,c+'('+
stuff((select ', '+d from example with (nolock)
where a = x.a and b = x.b and c=x.c for xml path(''))+')',1,1,'')
as level1 from example as x with (nolock) group by a,b,c) y
where a = z.a and b = z.b
for xml path('')),1,1,'') as level2
from
(select a,b from example with (nolock) group by a,b)z
Finally getting back to this post...
The only result I get back from your code using the test data you provided is an error.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ', ' to data type int.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply