June 18, 2014 at 5:05 am
I have one table dbo.t1 with data like this:
Col1Col2Col3
XYZ12RXM
XYZ13XUV
XYZ14RXM
MOP22RXM
MOP24RXM
Now i'm selecting only one record per Col1 with RXM value in Col3 having maximum of Col2. Result is like this:
Col1Col2Col3
XYZ14RXM
MOP24RXM
Now the issue is that I've one extra column where rest of the Col2 values per Col1 has to be updated like this:
Col1Col2Col3NewCol
XYZ14RXM12(RXM);13(XUV)
MOP24RXM22(RXM)
i've tried but not succeeded.
June 18, 2014 at 5:11 am
Post the query you're using for the first result set, and the usual sample data. I'd guess a combination of ROW_NUMBER() to identify the rows to keep, and FOR XML PATH to concatenate the rows to drop.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2014 at 5:45 am
I was able to get the much needed solution. Here are the queries to generate the data:
create table #t11
(col1 varchar(10), col2 int, col3 varchar(10))
insert into #t11
select 'XYZ', 12, 'RXM' union all
select 'XYZ', 13, 'XUV' union all
select 'XYZ', 14, 'RXM' union all
select 'MOP', 22, 'RXM' union all
select 'MOP', 24, 'RXM'
select * from #t11
;WITH cte1
AS
(
SELECT col1, max(col2) as col2
FROM #t11
WHERE col3 = 'RXM'
group by col1
)
select b.*, convert(varchar(255), null) as col4
into #temp11
from cte1 a
inner join #t11 b on a.col2 = b.col2
Here is how i got the result. Thanks to google.
update x
set x.col4 = stuff((select ';'+ cast(a.col2 as varchar)+'('+a.col3+')'
from #t11 a
left join #temp11 b
on a.col2 = b.col2
where b.col2 is null
and a.col1 = x.col1
for xml path('')), 1, 1, '')
FROM #temp11 x
June 18, 2014 at 6:34 am
sqlnaive (6/18/2014)
I was able to get the much needed solution. Here are the queries to generate the data:create table #t11
(col1 varchar(10), col2 int, col3 varchar(10))
insert into #t11
select 'XYZ', 12, 'RXM' union all
select 'XYZ', 13, 'XUV' union all
select 'XYZ', 14, 'RXM' union all
select 'MOP', 22, 'RXM' union all
select 'MOP', 24, 'RXM'
select * from #t11
;WITH cte1
AS
(
SELECT col1, max(col2) as col2
FROM #t11
WHERE col3 = 'RXM'
group by col1
)
select b.*, convert(varchar(255), null) as col4
into #temp11
from cte1 a
inner join #t11 b on a.col2 = b.col2
Here is how i got the result. Thanks to google.
update x
set x.col4 = stuff((select ';'+ cast(a.col2 as varchar)+'('+a.col3+')'
from #t11 a
left join #temp11 b
on a.col2 = b.col2
where b.col2 is null
and a.col1 = x.col1
for xml path('')), 1, 1, '')
FROM #temp11 x
Your base data is read an alarming number of times using this interesting set of queries. Try this instead:
WITH OrderedData AS
(SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2 DESC) FROM #t11)
SELECT o.col1, o.col2, o.col3, x.col4
FROM OrderedData o
CROSS APPLY (
SELECT col4 = STUFF(
(SELECT ';' + cast(oi.col2 as varchar)+'('+oi.col3+')'
FROM OrderedData oi
WHERE oi.col1 = o.col1 AND oi.rn > 1
ORDER BY oi.col2
FOR XML PATH(''))
,1,1,'')
) x
WHERE o.rn = 1
ORDER BY o.col1 DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2014 at 7:27 am
Thanks Chris. it works like charm. Though I'll check the performance of both on big data sets and will post here as well. 🙂
June 19, 2014 at 3:10 am
I have some enhanced requirement now and it seems little more tricky than previous one. Half of the work is done but i'm stuck at one place. So i have a query like this:
--drop table #table1
--drop table #table2
--drop table #temp1
create table #table1
(Col1 varchar(10), Col2 int, Col3 varchar(20))
insert into #table1
select 'ABC', 1121, 'RXTC' union all
select 'ABC', 1122, 'RXTC' union all
select 'ABC', 1123, 'RXTC' union all
select 'ABC', 1124, 'RXTC' union all
select 'ABC', 1125, 'RXTC' union all
select 'ABC', 1126, 'RXTC' union all
select 'ABC', 1127, 'RXTC' union all
select 'ABC', 2001, 'INCD' union all
select 'ABC', 2002, 'INCD' union all
select 'ABC', 2003, 'INCD' union all
select 'ABC', 5820, 'INCD' union all
select 'ABC', 5821, 'MOPS' union all
select 'ABC', 5822, 'MOPS' union all
select 'ABC', 5823, 'MOPS' union all
select 'ABC', 5824, 'MOPS' union all
select 'ABC', 5825, 'MOPS' union all
select 'XYZ', 115820, 'INCD' union all
select 'XYZ', 115821, 'KKCB' union all
select 'XYZ', 115822, 'MOPS' union all
select 'XYZ', 115823, 'MOPS' union all
select 'XYZ', 115824, 'INCD' union all
select 'XYZ', 115825, 'RXTC'
--select * from #table1
create table #table2
(Col1 varchar(10), Col2 int, Col3 varchar(20), Col4 varchar(255), Col5 varchar(255))
insert into #table2
(Col1, Col2, Col3)
select a.Col1, a.Col2, a.Col3
from #table1 a
inner join
(select Col1, max(Col2) Col2
from #table1
where Col3 = 'INCD'
group by Col1) b
on a.Col1 = b.Col1
and a.Col2 = b.Col2
--select * from #table2
WITH OrderedData AS
(select Col1, Col3, Col2 = Count(Col2), ROW_NUMBER() OVER(partition by Col1 order by col1, col3) Sno
from #table1
group by Col1, Col3)
select o.col1, o.col3, o.col2, x.col4
into #temp1
from OrderedData o
cross apply (
SELECT col4 = STUFF(
(SELECT ';' + cast(oi.col3 as varchar)+'('+CAST(oi.col2 AS VARCHAR)+')'
FROM OrderedData oi
WHERE oi.col1 = o.col1
ORDER BY oi.col3
FOR XML PATH(''))
,1,1,'')
) x
where o.col3 = 'INCD'
ORDER BY o.col1 DESC
update a
set a.Col4 = b.Col4
from #table2 a
inner join #temp1 b on a.Col1 = b.Col1
select * from #table2
Now i'm getting data as follows:
Col1Col2Col3Col4Col5
ABC5820INCDINCD(4);MOPS(5);RXTC(7)NULL
XYZ115824INCDINCD(2);KKCB(1);MOPS(2);RXTC(1)NULL
So here Col4 looks good. but i need to populate Col5 as well in following way:
Col1Col2Col3Col4Col5
ABC5820INCDINCD(4);MOPS(5);RXTC(7)MOPS(5821,5822,5823,5824,5825);RXTC(1121,1122,1123,1124,1125)
XYZ115824INCDINCD(2);KKCB(1);MOPS(2);RXTC(1)KKCB(115821);MOPS(115822,115823);RXTC(115825);INCD(115820,115824)
The point here is that in Col4 we are showing the number of Col3 instances per Col1. But in Col5, we have to show those values but restricted them to 10 items with values of "INCD" being lowest. Rest all should be shown in ascending order. Items more than 10 should not appear.
June 19, 2014 at 4:28 am
Add this to the end of your query (you better use code="sql" tag to format your code when posting here, otherwise your query end up as single line...):
;WITH tall
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY CASE WHEN Col3 = 'INCD' THEN 1 ELSE 0 END, Col3, Col2) RN
FROM #table1)
, t10
AS
(SELECT * FROM tall WHERE RN <= 10)
, t35
AS
(
SELECT DISTINCT t1.Col1, t1.Col3, Col3 + '(' + STUFF((SELECT ',' + CAST(t2.col2 AS VARCHAR)
FROM t10 t2 WHERE t2.Col1 = t1.Col1 and t2.Col3 = t1.Col3
ORDER BY t2.RN FOR XML PATH('')),1,1,'') + ')' Col5x
FROM t10 t1
)
UPDATE t2
SET Col5 = cte.Col5
FROM #table2 t2
JOIN (SELECT DISTINCT t1.Col1, STUFF((SELECT ';' + CAST(t2.col5x AS VARCHAR)
FROM t35 t2 WHERE t2.Col1 = t1.Col1
ORDER BY CASE WHEN t2.Col3 = 'INCD' THEN 1 ELSE 0 END, t2.Col3
FOR XML PATH('')),1,1,'') Col5
FROM t35 t1) cte
ON cte.Col1 = t2.Col1
SELECT * FROM #table2
June 19, 2014 at 4:39 am
Thanks a ton Eugene. 🙂 It's beautiful.
I'll take care of the point you made about posting code.
June 19, 2014 at 4:44 am
sqlnaive (6/19/2014)
Thanks a ton Eugene. 🙂 It's beautiful.I'll take care of the point you made about posting code.
I wouldn't call the above (my) code "beautiful", it looks like a pot of noodles to me.
I do hope that you will find a way to make it much neatter...
🙂
June 19, 2014 at 8:37 am
It's beautiful becuase it is working as expected. Definitely i'll try to see if it can be made more healthy. I still have to check how it works with the entire population. So will keep this thread posted. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply