SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evets
Starting Member

3 Posts

Posted - 12/20/2012 :  08:45:24  Show Profile  Reply with Quote
Hello,
I must create a query usable through excel, pointing a sql 2005.

I have a similar situation:

CLIENTS
-------
ID
---
1
2
3
4

PROTOCOLS
------------------
IDCLIENT | NUMBERS
------------------
1 AA
1 BB
2 XX
2 YY
2 ZZ



I wish to obtain

Clients Protocols
---------------------------
1 AA, BB
2 XX, YY, ZZ

or, at least:

Clients Protocols Protocols2 PRotocols3 ...
-----------------------------------------------------------------
1 AA BB
2 XX YY ZZ ...


I Cannot forsee how many occurrencies of each record I will have, but I could set a Maximum (5 or 10 max).

Is there a way to obtain this ?

Thank you

Stefano

Edited by - evets on 12/20/2012 11:08:35

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/20/2012 :  10:49:48  Show Profile  Reply with Quote
Check the bottom of this post on how to pivot dynamic

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181589
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/20/2012 :  11:19:13  Show Profile  Reply with Quote
for former output use

SELECT Clients,
STUFF((SELECT ',' + NUMBER FROM PROTOCOLS WHERE IDCLIENTS = c.ID FOR XML PATH('')),1,1'') AS Protocols
FROM CLIENTS c



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

evets
Starting Member

3 Posts

Posted - 12/20/2012 :  11:35:09  Show Profile  Reply with Quote
Thank you but I obtain this:

SELECT prpratiche,
STUFF((SELECT ',' + att_descrizione FROM agattivita
WHERE att_idpratica = c.pra_id FOR XML PATH('')),1,1'') AS Protocols
FROM prpratiche c


Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ''.


thank you very much !

quote:
Originally posted by visakh16

for former output use

SELECT Clients,
STUFF((SELECT ',' + NUMBER FROM PROTOCOLS WHERE IDCLIENTS = c.ID FOR XML PATH('')),1,1'') AS Protocols
FROM CLIENTS c



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/20/2012 :  11:43:09  Show Profile  Reply with Quote
missed a comma


SELECT prpratiche,
STUFF((SELECT ',' + att_descrizione FROM agattivita 
WHERE att_idpratica = c.pra_id FOR XML PATH('')),1,1,'') AS Protocols
FROM prpratiche c


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/20/2012 :  11:44:26  Show Profile  Reply with Quote
quote:
Originally posted by evets

Thank you but I obtain this:

SELECT prpratiche,
STUFF((SELECT ',' + att_descrizione FROM agattivita
WHERE att_idpratica = c.pra_id FOR XML PATH('')),1,1,'') AS Protocols
FROM prpratiche c


Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ''.


thank you very much !

quote:
Originally posted by visakh16

for former output use

SELECT Clients,
STUFF((SELECT ',' + NUMBER FROM PROTOCOLS WHERE IDCLIENTS = c.ID FOR XML PATH('')),1,1'') AS Protocols
FROM CLIENTS c



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000