TechnologyTypeSize
XYZA200
XYZ1A200
XYZ2A300
XYZ3A300
ABC1X238
ABC2X238
PQRB320
MNOC330
I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.
Now I need to concatenate the Technology based on Type and size.
As you can see in Type A we have two sizes 200 and 300.
We need to group the Technology of type A with same size together.
So the output of the procedure should be
XYZ + XYZ1
XYZ2+ XYZ3
ABC1 + ABC2etc.
We need to concatenate the Technology string with the next technology if they have the same type and size.
Can somebody please help or send any sample code.
Any help is greatly appreciated
Thanks
Swapna
CTE solution for SQL Server 2005:
With MyCTE(Size, Type, col1, col2, myNum) AS
(
SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum
FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))
UNION ALL
SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum
FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type
WHERE b.Technology>c.col1
)
SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1
GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax
|||you I am new to stored procedures...and working with the databse...so could you please explain the above code...I could not get much from it...Will the loop through the sample table I mentioned and return a set of concatenated Technology values....Please get back.
Thanks for your reply
Swapna
|||and more over the data in the table is just an example...we are in no way concerned with the data in Technology Column...all we need to do is group the technology column data which have the same Type and Size
TechnologyTypeSize
XYZA200
ABCA200
ABC1A300
XYZ3A300
MNO1X238
ABC2X238
PQRB320
MNOC330
so the output should be XYZ+ABC
ABC1+XYZ3
MNO1+ABC2.... I hope I am clear now.
Please reply...Can we use cursors to do this...can someone explain how to use cursors for the above functionality
Thanks
|||
Hello:
The "techTable" would be the name of your table which holds your data.
The CTE code I posted will work in a recursive fasion.
If you are using SQL Server 2005, you can give the code a try run (remember to change the "techTable" to your table name).
|||--CREATE TABLE MyTable(Technology VARCHAR(MAX), Type char(10), Size int)
--Enter the values suggested
--Run the following code
DECLARE @.Type CHAR(1)
DECLARE @.Size INT
DECLARE @.MyNewString CHAR(11)
DECLARE @.MyNewString2 VARCHAR(MAX)
SET @.MyNewString2 = ''
--Replace MyTable with your tablename
--Replace Technology, Type, Size with your field names
CREATE TABLE #Temp(MyNewString VARCHAR(MAX))
DECLARE c1 CURSOR FOR
SELECT mt.Type, mt.Size
FROM MyTable mt
OPEN c1
FETCH NEXT FROM c1
INTO @.Type, @.Size
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT Technology from MyTable Where size = @.Size and type = @.Type
OPEN c2
FETCH NEXT FROM c2
INTO @.MyNewString
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.MyNewString2 = LTRIM(RTRIM(@.MyNewString2)) + LTRIM(RTRIM(@.MyNewString))
FETCH NEXT FROM c2
INTO @.MyNewString
END
CLOSE c2
DEALLOCATE c2
INSERT INTO #Temp(MyNewString) VALUES(@.MyNewString2)
SET @.MyNewString2 = ''
FETCH NEXT FROM c1
INTO @.Type, @.Size
END
CLOSE c1
DEALLOCATE c1
SELECT * from #Temp
GROUP BY MyNewString
DROP TABLE #temp
|||This code is equivalent to my nested cursor approach and works, but I agree is a tad bit confusing...but nice work all the same.|||limno wrote:
CTE solution for SQL Server 2005:
With MyCTE(Size, Type, col1, col2, myNum) AS
(
SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum
FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))
UNION ALL
SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum
FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type
WHERE b.Technology>c.col1
)
SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1
GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax
You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.
If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.
select t2.Type
, t2.Size
, max(case t2.seq when 1 then t1.Technology end)
+ max(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology
from (
select t1.Type, t1.Technology, t1.Size
, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq
from tbl as t1
) as t2
group by t2.Type, t2.Size;
You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.
Below working query uses pubs authors table and you can do the same based on your table schema.
select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;
You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.
If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.
select t2.Type
, t2.Size
, min(case t2.seq when 1 then t1.Technology end)
+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology
from (
select t1.Type, t1.Technology, t1.Size
, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq
from tbl as t1
) as t2
group by t2.Type, t2.Size;
You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.
Not knowing cursors is a good thing? Can we go a step further with your logic and say not knowing SQL is a good thing? Use ADO?
...and could you post some working code. I'm interested in this approach but getting errors.
Thanks,
Adamus
|||I unmarked this as the answer because the poster requested a cursor approach.|||You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.
If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.
select t2.Type
, t2.Size
, min(case t2.seq when 1 then t1.Technology end)
+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology
from (
select t1.Type, t1.Technology, t1.Size
, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq
from tbl as t1
) as t2
group by t2.Type, t2.Size;
You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.
Not using procedural logic when dealing with SQL is a good thing. Yes, you can use ADO/client-side code to do this but it will be very slow and inefficient. If you have a table that contains say millions of rows you will be moving those rows from client to server for each user and performing the logic on the client side. Moreover, you have to implement lot of specific logic on the client side whereas the SQL language has built-in functionality / primitives to solve complex problems easily.
Anyway, here is a query that uses pubs authors table:
select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;
The query produces a comma-separated list of author ids for each state and city combination similar to the problem.
