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
 SQL Query help

Author  Topic 

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-04-22 : 00:30:46
Hello,

Anyone can help me with the solution?

I want to show each student with their purchaseID. Each customer may have more than one PurchaseID.

Here are the list of PurchadeID:
G4T
Y7M
6TK
8UL
5TF
R2T
S7D
K9T
9RF

Here are the list of CustomerName:
Ron
Kathy
Frank
Sam

Here is the select query:
SELECT CustomerName, PurchaseID
FROM TBLName1

Output:
Ron G4T
Ron 8UL
Ron 9RF
Ron 6TK
Ron Y7M
Ron S7D
Ron 5TF
Kathy S7D
Kathy K9T
Frank G4T
Frank 6TK
Frank Y7M
Frank S7D
Frank 5TF
Sam G4T
Sam S7D

But I want the output display like this:
CustomerName PurchaseID
Ron G4T,8UL, 9RF, 6TK, Y7M, 5TF
Frank G4T, 6TK, Y7M, 5TF
Sam G4T, S7D

Kathy does not included because it does not have the PurchaseID of G4T.

How do I update the query so each student only appear one row and each student MUST have PurchaseID G4T. Display other PurchaseIDs except S7D (as shown as above) ?

Anyone know how to write the sql query for this?



SQLBoy

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-04-22 : 00:41:03
syntax of stuff:

SELECT Customername,STUFF((SELECT','+PurchaseId FROM table1 WHERE table1.CustomerName = table2.CustomerName
FOR XML PATH('')),1,1,'') FROM table2

Veera
Go to Top of Page

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-04-22 : 09:22:07
So, the table1 and table2 need to be declared as temporary table? Thanks Veera

SQLBoy
Go to Top of Page

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-04-22 : 09:53:56
also, how do I filter that student MUST have PurchaseID G4T, so if student does not have PurchaseID G4T, they will eliminate from the display? Kathy is one of a good sample because she does not have PurchaseID G4T. Thanks Veera.

SQLBoy
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-04-23 : 22:34:24
add a where clause

WHERE EXISTS (SELECT 1 FROM table1 c WHERE c.CustomerName = table2.CustomerName AND PurchaseId = 'G4T')
Go to Top of Page

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-04-24 : 00:53:20
Thank you Veera and waterduck. I will try it.

SQLBoy
Go to Top of Page
   

- Advertisement -