query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

namratadas
Starting Member

4 Posts

Posted - 2011-06-30 : 19:55:53

I have a table
PartOrderId PackOrderId
--------- -------------
1735 447
1735 481
1736 447
1736 482
1737 447
1737 482
1738 447
1738 482
1739 447
1739 482
1740 447
1740 482
1741 447
1741 482
1742 447
1742 482
1743 447
1743 482
1743 498
1744 438
1744 447
1744 482
1744 498

I have to write a query where for each PartOrderId, concatenate the PackOrderId into one column.ie the expected output should be as follows:
PartOrderId Expected result
----------- -----------------
1735 447481
1736 447482
1737 447482
1738 447482
1739 447482

I am using sql server 2000.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-01 : 06:34:51

see
http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

select PartOrderId ,
csvstr = (
select '' + convert(varchar(20,PackOrderId)
from tbl t2 where t.PartOrderId = t2.PartOrderId
for xml path('')
)
from (select distinct PartOrderId from tbl) t

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

namratadas
Starting Member

4 Posts

Posted - 2011-07-01 : 13:03:27

I am not sure if i can use XML path in SQL server 2000? This solution might not work for me.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-01 : 14:25:27

DECLARE @table TABLE(PartOrderId int, PackOrderId int)
INSERT @table

select 1735, 447 union all
select 1735, 481union all
select 1736, 447union all
select 1736 ,482union all
select 1737, 447 union all
select 1737, 482 union all
select 1738, 447 union all
select 1738, 482 union all
select 1739, 447 union all
select 1739, 482

select t1.PartOrderId, convert(varchar(10),t1.PackOrderId) + convert(varchar(10),t2.PackOrderId)
from @table t1
left join @table t2 on t1.PartOrderId =t2.PartOrderId

Jim

Everyday I learn something that somebody else already knew

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-07-01 : 14:53:01

drop table #tmppmd
-- create table
select '1735' as PartOrderId,'447' as PackOrderId,cast(' ' as char(100)) as outvar
into #tmppmd
insert into #tmppmd values ('1735','481',' ')
insert into #tmppmd values ('1736','447',' ')
insert into #tmppmd values ('1736','482',' ')
insert into #tmppmd values ('1737','447',' ')
insert into #tmppmd values ('1737','482',' ')
insert into #tmppmd values ('1738','447',' ')
insert into #tmppmd values ('1738','482',' ')
insert into #tmppmd values ('1739','447',' ')
insert into #tmppmd values ('1739','482',' ')
insert into #tmppmd values ('1740','447',' ')
insert into #tmppmd values ('1740','482',' ')
insert into #tmppmd values ('1741','447',' ')
insert into #tmppmd values ('1741','482',' ')
insert into #tmppmd values ('1742','447',' ')
insert into #tmppmd values ('1742','482',' ')
insert into #tmppmd values ('1743','447',' ')
insert into #tmppmd values ('1743','482',' ')
insert into #tmppmd values ('1743','498',' ')
insert into #tmppmd values ('1744','438',' ')
insert into #tmppmd values ('1744','447',' ')
insert into #tmppmd values ('1744','482',' ')
insert into #tmppmd values ('1744','498',' ')

declare
@outvar char(100),
@PartOrderId char(4)

select
@PartOrderId = '',
@outvar = ''

update
#TMPPMD
set
@outvar = outvar = case when @PartOrderId <> PartOrderId then rtrim(PartOrderId) + ' ' + rtrim(PackOrderId) else RTRIM(@outvar) + rtrim(PackOrderId) end,
@PartOrderId = PartOrderId

select max(outvar) as out
from #tmppmd
group by partorderid

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-07-01 : 14:53:56

FYI make sure input table is sorted or results will be unexpected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources