We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Query Help...

Medium Priority
277 Views
Last Modified: 2012-08-13
I would like to make this:
59      368      APR Tuned DirectPort ECU Upgrade      (02-05 Audi A4, 00-06 Audi TT, 99-06 VW Jetta, 99-06 VW Golf, 01-06 VW Beetle, 01-05 VW Passat) <- One Column

out of this:
PartID      CatID      PartName      PartMake      PartModel      PartYear
59      368      APR Tuned DirectPort ECU Upgrade      Audi      A4      2002
59      368      APR Tuned DirectPort ECU Upgrade      Audi      A4      2003
59      368      APR Tuned DirectPort ECU Upgrade      Audi      A4      2004
59      368      APR Tuned DirectPort ECU Upgrade      Audi      A4      2005
59      368      APR Tuned DirectPort ECU Upgrade      Audi      TT      2000
59      368      APR Tuned DirectPort ECU Upgrade      Audi      TT      2001
59      368      APR Tuned DirectPort ECU Upgrade      Audi      TT      2002
59      368      APR Tuned DirectPort ECU Upgrade      Audi      TT      2003
59      368      APR Tuned DirectPort ECU Upgrade      Audi      TT      2004
59      368      APR Tuned DirectPort ECU Upgrade      Audi      TT      2005
59      368      APR Tuned DirectPort ECU Upgrade      Audi      TT      2006
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      1999
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      2000
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      2001
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      2002
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      2003
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      2004
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      2005
59      368      APR Tuned DirectPort ECU Upgrade      VW      Jetta      2006
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      1999
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      2000
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      2001
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      2002
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      2003
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      2004
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      2005
59      368      APR Tuned DirectPort ECU Upgrade      VW      Golf      2006
59      368      APR Tuned DirectPort ECU Upgrade      VW      Beetle      2001
59      368      APR Tuned DirectPort ECU Upgrade      VW      Beetle      2002
59      368      APR Tuned DirectPort ECU Upgrade      VW      Beetle      2003
59      368      APR Tuned DirectPort ECU Upgrade      VW      Beetle      2004
59      368      APR Tuned DirectPort ECU Upgrade      VW      Beetle      2005
59      368      APR Tuned DirectPort ECU Upgrade      VW      Beetle      2006
59      368      APR Tuned DirectPort ECU Upgrade      VW      Passat      2001
59      368      APR Tuned DirectPort ECU Upgrade      VW      Passat      2002
59      368      APR Tuned DirectPort ECU Upgrade      VW      Passat      2003
59      368      APR Tuned DirectPort ECU Upgrade      VW      Passat      2004
59      368      APR Tuned DirectPort ECU Upgrade      VW      Passat      2005

Can anyone help me do this?
I will post the code I have as I build it.
Comment
Watch Question

Explain in detail how you want the output
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
try
SELECT  PartID, CatID, PartName,
        Stuff((Select  ',' + PartMake + ' ' + PartModel
               From Table1 T2
               Where T1.PartID = T2.PartID
               For xml Path('')
              ), 1, 1, '') as Details                 
                        
From    Table1 T1
Group By PartID, CatID, PartName

Open in new window

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
please see my initial question, the second line is the output
to re-iterate:

Output =

PartID      CatID      PartName      PartMakeModelYears
59      368      APR Tuned DirectPort ECU Upgrade      (02-05 Audi A4, 00-06 Audi TT, 99-06 VW Jetta, 99-06 VW Golf, 01-06 VW Beetle, 01-05 VW Passat)
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
Modify this to
SELECT  PartID, CatID, PartName,
        Stuff((Select  ',' + PartMake + ' ' + PartModel
               From Table1 T2
               Where T1.PartMake = T2.PartMake
               and T1.PartModel = T2.PartModel  
               For xml Path('')
              ), 1, 1, '') as Details                 
                        
From    Table1 T1
Group By PartID, CatID, PartName, PartMake, PartModel

Open in new window

Author

Commented:
Sorry, I should have been more specific

Parts are one table
Make is one table
Model is one table
Years is one table

Author

Commented:
and my initial query to get the data in the question was:
SELECT     a.partID, a.catID, a.partName, b.pvMake, c.pvModel, d.pvYear
FROM       TblParts a
Inner Join TblPartVehicleMake b On b.partID = a.partID
Inner Join TblPartVehicleModel c On c.pvMakeID = b.pvMakeID
Inner Join TblPartVehicleModelYear d On d.pvModelID = c.pvModelID
Where a.partID = 59
CERTIFIED EXPERT

Commented:
For my result, just change the query in the function to return the data you need to aggregate.

Author

Commented:
Ok....  getting somewhere:
SELECT a.partID, a.catID, a.partName, b.pvMake,
(Convert(Xml,
      (Select Distinct m.pvModel
            As MakeModelYear
            From vwSelectPartVehcileModels m
            Where m.pvMakeID = b.pvMakeID
            For Xml Path('')))) As MakeModeYear
FROM vwSelectPart a
Inner Join vwSelectPartVehcileMakes b On b.partID = a.partID
Where a.partID = 59

Returns Me:
59      368      APR Tuned DirectPort ECU Upgrade      Volkswagen      <MakeModelYear>Beetle</MakeModelYear><MakeModelYear>Golf</MakeModelYear><MakeModelYear>Jetta</MakeModelYear><MakeModelYear>Passat</MakeModelYear>
59      368      APR Tuned DirectPort ECU Upgrade      Audi      <MakeModelYear>A4</MakeModelYear><MakeModelYear>TT</MakeModelYear>

Author

Commented:
Declare @Results NVarChar(Max)

     --a.partID, a.catID, a.partName,
Select     @Results = coalesce(@Results + ',', '') + CAST(d.pvYear as varchar(10)) + ' ' + b.pvMake + ' ' + c.pvModel)
--b.pvMake, c.pvModel, d.pvYear
FROM       TblParts a
Inner Join TblPartVehicleMake b On b.partID = a.partID
Inner Join TblPartVehicleModel c On c.pvMakeID = b.pvMakeID
Inner Join TblPartVehicleModelYear d On d.pvModelID = c.pvModelID
Where a.partID = 59

gets me errors

Author

Commented:
CREATE FUNCTION [dbo].[GroupData] (@PartID int)
RETURNS NVarChar(MAX) AS  
BEGIN

Declare @Results NVarChar(Max)

Select @Results = Coalesce(@Results + ',', '') + CAST(d.pvYear as varchar(10)) + ' ' + b.pvMake + ' ' + c.pvModel)
FROM TblParts a
Inner Join vwSelectPartVehcileMakes b On b.partID = a.partID
Inner Join vwSelectPartVehcileModels c On c.pvMakeID = b.pvMakeID
Inner Join vwSelectPartVehcileYears d On d.pvModelID = c.pvModelID
Where a.partID = @PartID
Group By partID

Return (@Results)

End

Incorrect syntax near ')'

Author

Commented:
Not quite what I had in mind lludden
ALTER FUNCTION [dbo].[PartVehicleMakeModelYear] (@PartID int)
RETURNS NVarChar(MAX) AS  
BEGIN

Declare @Results NVarChar(Max)

Select @Results = Coalesce(@Results + ',', '') + Convert(NVarChar(Max), d.pvYear) + ' ' + Convert(NVarChar(Max), b.pvMake) + ' ' + Convert(NVarChar(Max), c.pvModel)
FROM vwSelectPart a
Inner Join vwSelectPartVehcileMakes b On b.partID = a.partID
Inner Join vwSelectPartVehcileModels c On c.pvMakeID = b.pvMakeID
Inner Join vwSelectPartVehcileModelYears d On d.pvModelID = c.pvModelID
Where a.partID = @PartID
--Group By a.partID

Return (@Results)

End

results in:
59      368      APR Tuned DirectPort ECU Upgrade      2002 Audi A4,2003 Audi A4,2004 Audi A4,2005 Audi A4,2000 Audi TT,2001 Audi TT,2002 Audi TT,2003 Audi TT,2004 Audi TT,2005 Audi TT,2006 Audi TT,1999 VW Jetta,2000 VW Jetta,2001 VW Jetta,2002 VW Jetta,2003 VW Jetta,2004 VW Jetta,2005 VW Jetta,2006 VW Jetta,1999 VW Golf,2000 VW Golf,2001 VW Golf,2002 VW Golf,2003 VW Golf,2004 VW Golf,2005 VW Golf,2006 VW Golf,2001 VW Beetle,2002 VW Beetle,2003 VW Beetle,2004 VW Beetle,2005 VW Beetle,2006 VW Beetle,2001 VW Passat,2002 VW Passat,2003 VW Passat,2004 VW Passat,2005 VW Passat

Author

Commented:
This is the closest I have been able to get to get what I am after:
 
SELECT a.partID, a.catID, a.partName, 
(Convert(Xml, 
	(Select Distinct 
		(Select Convert(NVarChar(Max), MIN(Right(d.pvYear, 2))) + ' - ' + Convert(NVarChar(Max), MAX(Right(d.pvYear, 2)))
		From vwSelectPartVehcileModelYears d Where d.pvModelID = m.pvModelID) + ' ' + 
		b.pvMake + ' ' +
		m.pvModel
		As MakeModelYear
		From vwSelectPartVehcileModels m
		Where m.pvMakeID = b.pvMakeID
		For Xml Path('')))) As MakeModeYear
FROM vwSelectPart a
Inner Join vwSelectPartVehcileMakes b On b.partID = a.partID
Where a.partID = 59

Open in new window


It produces the following results:
PartID      CatID      PartName      PartMakeModelYears
59      368      APR Tuned DirectPort ECU Upgrade      <MakeModelYear>00 - 06 Audi TT</MakeModelYear><MakeModelYear>02 - 05 Audi A4</MakeModelYear>
59      368      APR Tuned DirectPort ECU Upgrade      <MakeModelYear>00 - 99 VW Golf</MakeModelYear><MakeModelYear>00 - 99 VW Jetta</MakeModelYear><MakeModelYear>01 - 05 VW Passat</MakeModelYear><MakeModelYear>01 - 06 VW Beetle</MakeModelYear>

I have tried to incorporate coalesce into this but have been unsucsessful (need the results to be only one row, just combining the values from the PartMakeModelYears into one column)
Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks!
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

u r welcome
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.