July 30, 2009 at 5:22 am
I'm trying to do something quite odd which may ring alarm bells. Anyway I've a table like this:
Key Value1 Value2 Value3
1 a b c
1 d e f
1 g h i
2 j k l
2 m n o
2 p q z
I want a resultset in the format
Key Values
1 abc;def;ghi
2 jkl;mno;pqz
I can do this procedurally with a cursor but I'm trying to avoid the use of cursors. Anyone got any clever ways of achieving this?
Thanks,
Andrew
July 30, 2009 at 5:37 am
Hi,
I Don't have sql server right now but you can achive this using coleasce
read this blog http://sqlservercoollinks.blogspot.com/2009/05/returning-comma-seperated-values-in-sql.html
Declare @vComma Varchar(max);
Select @vComma = COALESCE(@vComma+';','') + column1+Column2+column3 from table1
select @vComma
July 30, 2009 at 6:10 am
That's a great start. Thanks.
This statement 'serializes' the whole table. I need to be able to have one serialization per key field, if that makes sense.
I.e.
1 abc;def;ghi
2 jkl;mno;pqz
at the moment all I can get is abc;def;ghi;jkl;mno;pqz
Any ideas how I can split my resultset in this way?
Andrew
July 30, 2009 at 6:23 am
You can use a function to get this working
IF (SELECT OBJECT_ID('tempTable')) IS NOT NULL
DROP TABLE tempTable
GO
CREATE TABLE tempTable (test int, test2 VARCHAR(10))
INSERT INTO tempTable VALUES (1, 'Test')
INSERT INTO tempTable VALUES (2, 'Test2')
INSERT INTO tempTable VALUES (3, 'Test3')
INSERT INTO tempTable VALUES (4, 'Test4')
INSERT INTO tempTable VALUES (1, 'Test5')
INSERT INTO tempTable VALUES (2, 'Test6')
INSERT INTO tempTable VALUES (3, 'Test7')
INSERT INTO tempTable VALUES (4, 'Test8');
GO
IF (SELECT OBJECT_ID('testCombiningRows')) IS NOT NULL
DROP FUNCTION testCombiningRows
go
CREATE FUNCTION [dbo].[testCombiningRows]
(
@test-2 int
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @temp AS VARCHAR(100)
SELECT @temp = COALESCE(@temp+',','')+[test2] FROM tempTable
WHERE test = @test-2
RETURN @temp
END
GO
SELECT test, dbo.testCombiningRows(test) AS commaSeparated
FROM tempTable
GROUP BY test
You will have to change it based on your primary key but this should get you going.
July 30, 2009 at 6:30 am
Next time pleas create a small script that creates that table and insert the test data into it. This will save some time for anyone that wants to show you how to do what you asked.
The code bellow shows one way of doing it. The code takes advantage of the for xml clause. When you use the for xml clause, you get back one column in one row only. Because of this I can use the correlated sub query in the select clause. The empty string that I have after the I specify the path mode, causes the XML not to have an element name (you can play with to see how it works)
use tempdb
go
--Creating the table
create table demo (RoKkey tinyint, value1 char(1), value2 char(1), value3 char(1))
go
--Inserting the data
insert into demo (RoKkey, value1, value2, value3)
select 1, 'a', 'b', 'c'
union
select 1, 'd', 'e', 'f'
union
select 1, 'g', 'h', 'i'
union
select 2, 'j', 'k', 'l'
union
select 2, 'm', 'n', 'o'
union
select 2, 'p', 'q', 'r'
go
select distinct RoKkey, (select value1 + value2 + value3 + ';'
from demo as InnerDemo
where InnerDemo.RoKkey = Demo.RoKkey
for xml path(''))
from demo
go
drop table demo
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 30, 2009 at 7:43 am
July 30, 2009 at 8:32 am
Many thanks to all. I plumped for the XML variant in the end as it fits my existing stored procedure quite nicely.
Cheers,
Andrew
July 30, 2009 at 10:15 am
A slightly different version of the XML, if you want to get rid of that nagging semicolon at the end. Put the semicolons in front and then use STUFF to get rid of the first character of each string.
if OBJECT_ID(N'tempdb..#temp') is not null drop table #temp
create table #temp (pk int, Value1 char(1), Value2 char(1), Value3 char(1))
insert into #temp
select 1, 'a', 'b', 'c' union all
select 1, 'd', 'e', 'f' union all
select 1, 'g', 'h', 'i' union all
select 2, 'j', 'k', 'l' union all
select 2, 'm', 'n', 'o' union all
select 2, 'p', 'q', 'z'
select * from #temp
select PK,stuff((SELECT ';' + value1+value2+value3
FROM #temp t2
WHERE t2.PK = t1.PK -- must match GROUP BY below
ORDER BY value1+value2+value3
FOR XML PATH('')
),1,1,'') as [Concatenated]
from #temp t1
GROUP BY pk -- without GROUP BY multiple rows are returned
ORDER BY pk
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply