We help IT Professionals succeed at work.

Can you combine row data from multiple rows into one row in an access or sql server query

I don't know if this is possible but can you take these 3 rows of data and combine them into one row in an access or sql server query?

 query-results.xls
Comment
Watch Question

CERTIFIED EXPERT

Commented:
in SQL server

select       distinct
      a.ORDER_NAME      
      ,a.NC_PROCESS_STEP      
      ,a.DEFECT_TYPE      
      , stuff((select ', ' + ATTRIBUTE_NAME from yourtable where Order_name = a.Order_name), 1, 1, '')
from yourtable a


CERTIFIED EXPERT

Commented:
This will give a delimited list of the items in that column
change the delimiter as you need
SELECT S1.Order_name, S1.NC_PROCESS_STEP, S1.DEFECT_Type,
         stuff((
                    SELECT ';' + s2.Attribute_Name
                    FROM MyTable s2
                    WHERE s2.Order_Name = s1.Order_Name
                           AND S2.NC_PROCESS_STEP = S1.NC_PROCESS_STEP
                           AND S2.DEFECT_Type = S1.DEFECT_Type
                    FOR XML PATH ('')
                   ), 1, 1, '') AS AttributeList
FROM myTable s1
GROUP BY s1.Order_Name, S1.NC_PROCESS_STEP, S1.DEFECT_Type

CERTIFIED EXPERT

Commented:

oops,


select       distinct
      a.ORDER_NAME      
      ,a.NC_PROCESS_STEP      
      ,a.DEFECT_TYPE      
      , stuff((select ', ' + ATTRIBUTE_NAME from yourtable where Order_name = a.Order_name for xml path('')), 1, 1, '')
from yourtable a
CERTIFIED EXPERT
Top Expert 2016
Commented:
yes you can do that..
see this similar thread..

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26833591.html#a34933741

download the posted db and modify the query to suit your need

post back if encounter any problem..
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Try this example for table a (f1, f2, f3)
f1      f2      f3
1      2      a
1      2      b
1      2      c
2      3      d
2      3      e
2      3      f

Module function:
Function allIn1(c1 As Variant) As String
    Dim rs As Recordset
    Dim s As String
    s = ""
    Set rs = CurrentDb.OpenRecordset("Select f3 from a where f1 = '" & c1 & "'")
    rs.MoveFirst
    Do While Not (rs.EOF)
        s = s + rs(0) + " "
        rs.MoveNext
    Loop
    allIn1 = s
End Function

Query:
SELECT f1, f2, allIn1([f1]) AS joined
FROM a
GROUP BY f1, f2, allIn1([f1]);

Result:
f1      f2      joined
1      2      a b c
2      3      d e f

Explore More ContentExplore courses, solutions, and other research materials related to this topic.