?
Solved

SQL Query Help...

Posted on 2011-04-27
16
Medium Priority
?
258 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.
0
Comment
Question by:kevp75
  • 9
  • 4
  • 2
  • +1
16 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35478137
Explain in detail how you want the output
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35478181
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

0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 600 total points
ID: 35478182
Create a Function to produce concatenate the fields and use it in a query.

For example:
CREATE FUNCTION [dbo].[GroupData] (@PartID int, @CatID int)
RETURNS varchar(MAX) AS  
BEGIN
Declare @ResultList varchar(MAX)

SELECT     @ResultList = coalesce(@ResultList +',','') + CAST(PartYear \ 10 as varchar(10)) + ' ' + PartMake + ' ' + PartModel)
FROM myTable
WHERE PartID  = @PartID  AND CatID = @CatID
GROUP BY PartID,  CatID

Return (@ResultList )
END

Then in your query:
SELECT PartID, CatID, PartName, dbo.GroupData(PartID,CatID) as Summary
FROM myTable
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 25

Author Comment

by:kevp75
ID: 35478192
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)
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35478209
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

0
 
LVL 25

Author Comment

by:kevp75
ID: 35478211
Sorry, I should have been more specific

Parts are one table
Make is one table
Model is one table
Years is one table
0
 
LVL 25

Author Comment

by:kevp75
ID: 35478219
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
0
 
LVL 18

Expert Comment

by:lludden
ID: 35478417
For my result, just change the query in the function to return the data you need to aggregate.
0
 
LVL 25

Author Comment

by:kevp75
ID: 35478505
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>
0
 
LVL 25

Author Comment

by:kevp75
ID: 35478547
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
0
 
LVL 25

Author Comment

by:kevp75
ID: 35478585
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 ')'
0
 
LVL 25

Author Comment

by:kevp75
ID: 35478723
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
0
 
LVL 25

Author Comment

by:kevp75
ID: 35479039
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)
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 1400 total points
ID: 35479285

try this
;with CTE
as
(
	select a.PartID, b.pvMake, c.pvModel, 
	Right(CAST(MIN(d.PartYear) as varchar(4)), 2) + '-' + RIGHT(CAST(MAX(d.PartYear) as varchar(4)), 2) ModelYear
	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
	group by a.PartID, b.pvMake, c.pvModel
)

select PartID, CatID, PartName,
		Stuff((Select  ',' + ModelYear + ' ' + pvMake + ' ' + pvModel
               From CTE C
               Where C.PartID = vwSelectPart.PartID
               For xml Path('')
              ), 1, 1, '') as ModelMakeYear  
from vwSelectPart
group by PartID, CatID, PartName

Open in new window

0
 
LVL 25

Author Comment

by:kevp75
ID: 35479550
thanks!
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35479575

u r welcome
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question