March 15, 2010 at 10:52 pm
Hi,
I have a table where in particular street can have multiple seq number. Hence the seq numbers should be shown as a columns in selected.
[Code]
declare @jobs table (street varchar(100),seq int)
insert into @jobs 'A',1 union all
insert into @jobs 'A',2 union all
insert into @jobs 'B',11 union all
insert into @jobs 'B',12 union all
insert into @jobs 'C',33 union all
insert into @jobs 'C',44 union all
insert into @jobs 'D',5 union all
insert into @jobs 'D',3
[/Code]
Expected output should be
Street-s1-s2
-------------
A-1-2
B-11-12
C-33-44
D-5-3
Thanks.
March 16, 2010 at 1:30 am
Dear you can use below mentioned code for the same.
Using XML path function you can achieve this task.
-- Datas
DECLARE @jobs TABLE
(
street varchar(100),
seq int
);
insert @jobs VALUES ( 'A',1 )
insert @jobs VALUES ( 'A',2 )
insert @jobs VALUES ( 'B',11 )
insert @jobs VALUES ( 'B',12 )
insert @jobs VALUES ( 'C',33 )
insert @jobs VALUES ( 'C',44 )
insert @jobs VALUES ( 'D',5 )
insert @jobs VALUES ( 'D',3 )
SELECT * FROM @jobs
SELECT street, Substring(seq, 2, LEN(seq)) AS seq FROM
(
SELECT
[InnerData].street,
(SELECT '-' + cast(seq as varchar(10)) FROM @jobs WHERE street=[InnerData].street FOR XML PATH('')) AS seq
FROM
(
SELECT DISTINCT street FROM @jobs
) AS [InnerData]
) AS OuterData
March 16, 2010 at 2:09 am
You could use a cursor to solve the problem:
declare @jobs table (street varchar(100),seq int)
insert into @jobs
select 'A',1 union all
select 'A',2 union all
select 'B',11 union all
select 'B',12 union all
select 'C',33 union all
select 'C',44 union all
select 'D',5 union all
select 'D',3
declare @results table (value varchar(255))
declare streets cursor fast_forward
for select street,seq
from @jobs
order by street,seq
open streets
declare
@street varchar(100)
, @seq int
, @oldstreet varchar(100)
, @value varchar(max)
fetch next from streets
into @street, @seq
while @@fetch_status = 0
begin
if @oldstreet <> @street
begin
insert into @results values (@value)
set @value = null
end
set @value = isnull(@value,@street) + '-' + cast(@seq as varchar(11))
set @oldstreet = @street
fetch next from streets
into @street, @seq
end
insert into @results values (@value)
close streets
deallocate streets
select *
from @results
March 16, 2010 at 5:53 am
🙂
DECLARE @jobs TABLE
(
street varchar(100),
seq int
);
insert @jobs VALUES ( 'A',1 )
insert @jobs VALUES ( 'A',2 )
insert @jobs VALUES ( 'B',11 )
insert @jobs VALUES ( 'B',12 )
insert @jobs VALUES ( 'C',33 )
insert @jobs VALUES ( 'C',44 )
insert @jobs VALUES ( 'D',5 )
insert @jobs VALUES ( 'D',3 )
SELECT DISTINCT street
+'-'+ STUFF( (SELECT '-' + convert(varchar,seq) AS [text()] FROM @jobs b WHERE b.street = a.street FOR XML PATH(''))
, 1, 1, '' ) AS 'Street-s1-s2'
FROM @jobs a
ORDER BY 1
March 16, 2010 at 6:21 am
The XML PATH solution is a good solution, I just want to add a couple of things, and neaten things up a bit:
DECLARE @Jobs
TABLE (
street VARCHAR(100) NOT NULL,
seq INTEGER NOT NULL
);
INSERT @Jobs VALUES ('A', 1);
INSERT @Jobs VALUES ('A', 2);
INSERT @Jobs VALUES ('B', 11);
INSERT @Jobs VALUES ('B', 12);
INSERT @Jobs VALUES ('C', 33);
INSERT @Jobs VALUES ('C', 44);
INSERT @Jobs VALUES ('D', 5);
INSERT @Jobs VALUES ('D', 3);
SELECT result =
J1.street +
Seqs.xml_expr.value('/text()[1]', 'VARCHAR(MAX)')
FROM (
SELECT DISTINCT street
FROM @Jobs
) J1
CROSS
APPLY (
SELECT '-' + CONVERT(VARCHAR(12), seq)
FROM @Jobs J2
WHERE J2.street = J1.street
ORDER BY seq ASC
FOR XML PATH (''), TYPE
) Seqs (xml_expr)
ORDER BY
result ASC;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply