Rows to Columns Query Help... Please!!!

[adventurous1]

Hi...

Not very SQL savvy with this so any help is appreciated.

Two problems...

1. Case 1 - Data is structured like this:

ID Component
1 Hood
1 Body
1 Fender
2 Windshield
3 Bumper
3 Tire

I need to transform this to look like this:

ID Component
1 Hood, Body, Fender, Windshield, Bumper, Tire
2 Windshield
3 Bumper, Tire

2. Case 2 - Data is structured like this:

ID Component Status Insurance
1 Hood Fixed Allstate
1 Body In Process Geico
1 Fender Open State Farm
1 Window Fixed Allstate
2 Windshield Open State Farm
3 Bumper In Process Allstate
3 Tire In Process Geico

I need to transform this to look like this:

ID Fixed_Allstate_Comps In_Process_Geico Open_StateFarm
1 Hood, Window Body Fender
2 Windshield
3 Bumper Tire


Can anyone help me by posting code for this? I am trying to meet a deadline to save my contract so any help offered is appreciated.

 

[MajP]

Faq701-3499, Faq701-4233

 

[MajP]

qryConcatStatAndInsurance:
SELECT
tblClaims.ID,
tblClaims.component,
[status] & "_" & [insurance] AS StatusAndInsurance
FROM
tblClaims;

qryXTabClaims:
TRANSFORM
First(qryConcatStatAndInsurance.component) AS componentName
SELECT
qryConcatStatAndInsurance.ID
FROM
qryConcatStatAndInsurance
GROUP BY
qryConcatStatAndInsurance.ID
PIVOT
qryConcatStatAndInsurance.StatusAndInsurance;


Somebody smart can do it in one query.

 

[adventurous1]

Thanks MajP. I will try this for Case 2 above. What is needed for Case 1?

 

[MajP]

Those two FAQs give you the exact code.

 

[MajP]

You may not have noticed I posted the answers in two seperate posts. The FAQs are referenced in my first post. See it?

 

[adventurous1]

Missed the first one but got it now. I will try and close if I am successful. Thanks for the help!

 

[adventurous1]

This was not successful for either of the cases.

For the first one, I could not get the solutions from either of the links provided to work properly.

For the second, it only gives me the first value instead of all the values. This is a result of using "First" in the query below as you advised:

qryXTabClaims:
TRANSFORM
First(qryConcatStatAndInsurance.component) AS componentName
SELECT
qryConcatStatAndInsurance.ID
FROM
qryConcatStatAndInsurance
GROUP BY
qryConcatStatAndInsurance.ID
PIVOT
qryConcatStatAndInsurance.StatusAndInsurance;


Help please!

 

[MajP]

For the first one, I could not get the solutions from either of the links provided to work properly

What happened? Did you get an error message? Was the data in the wrong order? Did the system crash? Did the code not run? What was your error message? Which line of code broke?

Show us your code. We can not read minds.

 

[adventurous1]

I did get the first one to work today. It was a typo...

The second piece still does not provide all the values as it is limited to the first occurrence...not sure what to make of it. What additional info can I provide to help troubleshoot?

 

[MajP]

tblClaims

ID	component	status	insurance
1	hood	in process	geico
1	fender	complete	all state
1	brakes	in process	geico
2	body	in process	am gen
2	door	in process	am gen
3	wheel	in process	geico
3	tire	complete	nationwide

Public Function getConcatComp(id As Variant, status As Variant, insurance As Variant) As String
  Dim rs As DAO.Recordset
  Dim strSql As String
   
  If IsNumeric(id) And Not IsNull(status) And Not IsNull(status) Then
    strSql = "Select * from tblClaims where ID = " & id
    strSql = strSql & " AND status = '" & status & "'"
    strSql = strSql & " AND insurance = '" & insurance & "'"
    Set rs = CurrentDb.OpenRecordset(strSql)
 
    Do While Not rs.EOF
      getConcatComp = getConcatComp & rs!component & ","
      rs.MoveNext
    Loop
    If Not getConcatComp = "" Then
      getConcatComp = Left(getConcatComp, Len(getConcatComp) - 1)
    End If
  End If
End Function

qryConcatStatAndInsurance

SELECT tblClaims.ID, getconcatComp([ID],[Status],[Insurance]) AS component, [status] & "_" & [insurance] AS StatusAndInsurance
FROM tblClaims;

ID	component	StatusAndInsurance
1	hood,brakes	in process_geico
1	fender	complete_all state
1	hood,brakes	in process_geico
2	body,door	in process_am gen
2	body,door	in process_am gen
3	wheel	in process_geico
3	tire	complete_nationwide

TRANSFORM First(qryConcatStatAndInsurance.component) AS FirstOfcomponent
SELECT qryConcatStatAndInsurance.ID
FROM qryConcatStatAndInsurance
GROUP BY qryConcatStatAndInsurance.ID
PIVOT qryConcatStatAndInsurance.StatusAndInsurance;

ID complete_all state complete_nationwide in process_am gen in process_geico
1	fender			hood,brakes
2			body,door	
3		tire		wheel

The second piece still does not provide all the values as it is limited to the first occurrence

That makes no sense. If you concatenate the values all occurrences will be exactly the same. So makes no difference if it is the first last or any other occurrence.