MS SQL Server Query to combine multiple rows into one

Question

Trying to write a query for a database that models people. These people hold positions in the organization, and as such they have multiple entries in the positions table. I've also joined from tables that hold email, address, and phone information.

I have the original query similar to this one.

SELECT 
    P.ID
    , N.FIRST
    , N.MIDDLE
    , N.LAST
    , A.AD1
    , A.AD2
    , A.AD3
    , A.CITY
    , A.STATE
    , A.ZIP
    , A.COUNTRY
    , PH.NUMBER
    , PH.TYPE
    , E.TYPE
    , E.ADDRESS
FROM
    PERSON AS P
    JOIN NAME AS N ON P.ID=N.ID,
    JOIN ADDRESS AS A ON P.ID=A.ID,
    JOIN PHONE AS PH ON P.ID=PH.ID,
    JOIN EMAIL AS E ON P.ID=E.ID

I want to join the POSITION table onto this query, but instead of the join causing the original query to come out with a separate row for each row in the POSITION table, I would like each row from POSITION to be appended to the row from the query.

Query for the position table is simply for each of the rows returned from the first query, select * from Position as Pos where Pos.ID={P.id} where P.ID is from the first query.

A normal result should be like this:

kind of how you would think of joining an order record with the items on the order. I'm thinking this should be a union.

but what I get when I just join the position table is like this:

Is this possible? been looking for days.

Please post sample data, what you have tried etc. This link should help you: https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Eli, Aug 29 '17 at 18:56
I don't understand `query to come out with a separate row for each row ` you mean column? Add some data sample and expected results — Horaciux, Aug 29 '17 at 18:56
Yes, it's possible. It depends on if you want a single column of Position with the values being something like a [comma separated list](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server), or if you want a [new column for each Position](https://stackoverflow.com/q/14797691/6167855)... like Position1, Position2, etc... As @Horaciux stated, sample data and expected output. Regardless, your question is a duplicate of one of those two linked to my comment — S3S, Aug 29 '17 at 19:00
Please share join condition which you are using for "POSITION" table. — Rahul, Aug 29 '17 at 19:01
working on building the fiddle for you guys to see. Can't use the actual data for anonymity purposes. — Chris Rutherford, Aug 29 '17 at 19:06
Question is a little unclear, but if you're using SQL Server 2017, you might find the STRING_AGG function a bit useful. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql — EdmCoff, Aug 29 '17 at 19:06

LONG · Accepted Answer · 2017-08-30T12:16:03.210

1

Try this, not sure the joining condition and the column name, assume it is like below:

UPDATE for individual columns --assume you only have two position titles for each person

SELECT *, SUBSTRING(POS_TITLE,0,CHARINDEX(',',POS_TITLE,0)) as POS1_Title, SUBSTRING(POS_TITLE,CHARINDEX(',',POS)+1,LEN(POS_TITLE)) as POS2_Title
FROM
(
SELECT 
    P.ID
    , N.FIRST
    , N.MIDDLE
    , N.LAST
    , A.AD1
    , A.AD2
    , A.AD3
    , A.CITY
    , A.STATE
    , A.ZIP
    , A.COUNTRY
    , PH.NUMBER
    , PH.TYPE
    , E.TYPE
    , E.ADDRESS
    ,STUFF(
    (SELECT ','+title FROM POSITIONS as P WHERE P.PositionId = N.PositionId FOR XML PATH('')),1,1,'') as POS_TITLE
FROM
    PERSON AS P
    INNER JOIN NAME AS N ON P.ID=N.ID,
    INNER JOIN ADDRESS AS A ON P.ID=A.ID,
    INNER JOIN PHONE AS PH ON P.ID=PH.ID,
    INNER JOIN EMAIL AS E ON P.ID=E.ID
    INNER JOIN POSITIONS AS N ON N.ID = A.ID
GROUP BY P.ID
    , N.FIRST
    , N.MIDDLE
    , N.LAST
    , A.AD1
    , A.AD2
    , A.AD3
    , A.CITY
    , A.STATE
    , A.ZIP
    , A.COUNTRY
    , PH.NUMBER
    , PH.TYPE
    , E.TYPE
    , E.ADDRESS
) as A

edited Aug 30 '17 at 12:16

answered Aug 29 '17 at 19:44

LONG

4,490
2
17
35

This works! and I can get it going for all the columns, but a question. How would you do this if you wanted individual fields for each title instead of making a comma separated list? – Chris Rutherford Aug 29 '17 at 19:59
Oh, sorry, did not notice your desired output. then you probably need `PIVOT` or `CASE WHEN` statement, I am working on it. – LONG Aug 29 '17 at 20:03
@ChrisRutherford, check my update for up to 2 positions for each person, do not think it could be pivot :( – LONG Aug 29 '17 at 20:15
In that case might be best as a comma delimited list. There's a caring amount of positions for people – Chris Rutherford Aug 29 '17 at 20:18
@ChrisRutherford, yes, the only problem is the total number of positions is a dynamic value. – LONG Aug 30 '17 at 12:17

MS SQL Server Query to combine multiple rows into one

1 Answers1