Moving rows with multiple return data to columns to ensure one row from results in SQL Server

Question

I currently have a join that can results in multiple rows. Instead of using a join to get return results I would like to only return one but a particular column from the join table to be listed out in columns to show each one with only the one row.

select u.city, u.state, u.county, u.zip, c.local_code
from usa u
left join code c where c.zip = u.zip 
where zip = '90210'

Sample result

city       state   county         zip     local_code
----------------------------------------------------
salt lake  utah    lake county    90210   12A
salt pond  utah    lake county    90210   12C
sea salt   utah    lake county    90210   12B

Since there are multiple cities for that one zip, I would like to split that up into separate columns instead and name them local_code_1 - local_code_6 to fill any potential codes that come through in one row and remove the city name.

So I would like results like:

state   county         zip     local_code_1 local_code_2 local_code_3 local_code_4 local_code_5 local_code_6
utah    lake county    90210   12A          12C          12B

You could try with PIVOT, see an example [here](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Andrea, Aug 09 '18 at 13:40

score 0 · Answer 1 · answered Aug 09 '18 at 13:52

This code needs some work to complete your preferences, but I am thinking something like this might help you out. It's multiple CTE's combined, each of them joined by another rownumber.

;WITH CTE AS 
(
    SELECT U.CITY, U.STATE, U.COUNTY, U.ZIP, C.LOCAL_CODE
    FROM USA AS U
    LEFT JOIN CODE C WHERE C.ZIP = U.ZIP 
    WHERE ZIP= '90210'
)

SELECT *
FROM CTE AS C
LEFT JOIN CTE_JoinedTable AS C2 ON C.ID = C2.ID AND C2.RowNumb = 2
LEFT JOIN CTE_JoinedTable AS C3 ON C.ID = C3.ID AND C3.RowNumb = 3
LEFT JOIN CTE_JoinedTable AS C4 ON C.ID = C4.ID AND C3.RowNumb = 4
LEFT JOIN CTE_JoinedTable AS C5 ON C.ID = C5.ID AND C3.RowNumb = 5
LEFT JOIN CTE_JoinedTable AS C6 ON C.ID = C6.ID AND C3.RowNumb = 6
WHERE C.RowNumb = 1

score 0 · Answer 2 · answered Aug 09 '18 at 13:53

As already hinted by Andrea above, you are searching for a Pivot construction. You could assign a sequence nr by state, county and zip and then Pivot on this value.

DECLARE @t TABLE([city] VARCHAR(255),[state] VARCHAR(255),[county] VARCHAR(255),[zip] VARCHAR(25),[local_code] VARCHAR(25));
INSERT INTO @t VALUES
 ('salt lake','utah','lake county','90210','12A')
,('salt pond','utah','lake county','90210','12C')
,('sea salt','utah','lake county','90210','12B');

SELECT pvt.*
FROM (
     SELECT [state]
           ,[county]
           ,[zip]
           ,[local_code]
           ,[seq]=ROW_NUMBER() OVER(PARTITION BY [state],[county],[zip] ORDER BY [local_code] ASC)
     FROM @t
) src PIVOT(MAX(local_code) FOR seq IN([1],[2],[3],[4],[5],[6])) AS pvt;

Yielding

state           county          zip        1     2     3     4     5     6
--------------- --------------- ---------- ----- ----- ----- ----- ----- -----
utah            lake county     90210      12A   12B   12C   NULL  NULL  NULL

Zaynul Abadin Tuhin · Answer 3 · 2018-08-10T08:58:13.997

you can use pivot

select state,country,zip,[1] as local_code1,
[2] as local_code2,
[3] as local_code3,
[4] as local_code4,
[5] as local_code5,
[6] as local_code6
from 
(
   select state,country,zip,local_code,
    rn= row_number() over (partition by state,country,zip order by local_code)
    from t
  ) sr PIVOT (max(local_code) for rn in ([1],[2],[3],[4],[5],[6]) ) as pvt

http://sqlfiddle.com/#!18/358ae/40

 state  country     zip     local_code1     local_code2     local_code3     local_code4     local_code5     local_code6
utah   lake county 90210       12A            12B              12C            (null)        (null)        (null)

@Lynx does it helps? – Zaynul Abadin Tuhin Aug 10 '18 at 08:58 — Zaynul Abadin Tuhin, Aug 10 '18 at 08:58

Moving rows with multiple return data to columns to ensure one row from results in SQL Server

3 Answers3