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.
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:G4TY7M6TK8UL5TFR2TS7DK9T9RFHere are the list of CustomerName:RonKathyFrankSamHere is the select query:SELECT CustomerName, PurchaseIDFROM TBLName1Output:Ron G4TRon 8ULRon 9RFRon 6TKRon Y7MRon S7DRon 5TFKathy S7DKathy K9TFrank G4TFrank 6TKFrank Y7MFrank S7DFrank 5TFSam G4TSam S7DBut I want the output display like this:CustomerName PurchaseIDRon G4T,8UL, 9RF, 6TK, Y7M, 5TFFrank G4T, 6TK, Y7M, 5TFSam G4T, S7DKathy 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 table2Veera |
|
|
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 VeeraSQLBoy |
|
|
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 |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2014-04-23 : 22:34:24
|
add a where clauseWHERE EXISTS (SELECT 1 FROM table1 c WHERE c.CustomerName = table2.CustomerName AND PurchaseId = 'G4T') |
|
|
SQLBoy14
Yak Posting Veteran
70 Posts |
Posted - 2014-04-24 : 00:53:20
|
Thank you Veera and waterduck. I will try it.SQLBoy |
|
|
|
|
|
|
|