temporary table - 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

temporary table

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-11-27 : 09:01:15

Hi,

my code looks like this


Declare @Temp Table(id_customer int, id_product varchar(10))
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103')
Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102')
Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')

select * from @Temp
Declare @results VarChar(100) 
Select @results = IsNull(@results + '; ', '')  + IsNull(id_product, '')  
From   @Temp
where id_customer = 12 
Select  @results as all_products

and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(

thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-27 : 09:16:28

[code]select id_customer, @results as all_products
from @Temp[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-27 : 09:17:11

Hi try this
SELECT DISTINCT id_customer,
stuff((SELECT ';' + IsNull(id_product, '') FROM @Temp WHERE id_customer = t.id_customer FOR xml path('')), 1, 1, '') AS all_products
FROM @Temp t

and check the link once

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-11-27 : 09:30:43

Harsh Athalye: cleary it was a typing mistake :)

PeterNeo: thank you; would you be so kind and give me all the code :) it seems, that it doesn't work if I do
[CODE]
Declare @Temp Table(id_customer int, id_product varchar(10))
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103')
Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102')
Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
SELECT DISTINCT id_customer,
((SELECT ';' + IsNull(id_product, '') FROM @Temp
WHERE id_customer = t.id_customer FOR XML PATH('')), 1, 1, '') AS all_products
FROM @Temp t
[/CODE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 09:45:12

Read adam's article

Madhivanan

Failing to plan is Planning to fail

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-28 : 00:26:16

Hi u missed the STUFF key word in u r stmt

Declare @Temp Table(id_customer int, id_product varchar(10))
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103')
Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102')
Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
SELECT DISTINCT id_customer,
STUFF((SELECT ';' + IsNull(id_product, '') FROM @Temp
WHERE id_customer = t.id_customer FOR XML PATH('')), 1, 1, '') AS all_products
FROM @Temp t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources