Convert rows into Columns in SQL Server, T-SQL

Hi Guys,
I need to convert rows into columns. My requirement is that a postcode can have several addresses like below.

AB1 2DE , Address 1
AB1 2DE, Address 2
AB1 2DE, Address 3
.................................
.................................
AB1 2DE, Address N
AB2 1DE, Address 1
''''''''''''''''''''''''''''''''''''''''
AB2 1DE, Address N

I want to display all the addresses of a postcode as columns
AB12DE Address1 Address2 Address3 .........................Address N
AB2 1DE Address1 Address2 Address3 .........................Address N

Please note i am using SQL Server 2014

Kindest regards
Ali ShahSQL DeveloperAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
ALL addresses of a postcode as columns......
What is the maximum number of addresses you have to deal with?

select max(n) as maxnum
from (
    select postcode, count(*) as n from yourtable group by postcode
   ) d

Only if that number is reasonably small should you "pivot" rows into columns
i.e. if maxnum is hundreds or thousands would you really want to try it?

You need "dynamic sql" to achieve what you want, e.g.

DECLARE @cols AS nvarchar(max)
DECLARE @query AS nvarchar(max)

SET @cols = STUFF((
      SELECT DISTINCT
            ',' + QUOTENAME(address)
      FROM yourtable
      FOR xml PATH (''), TYPE
)
.value('.', 'NVARCHAR(MAX)')
, 1, 1, '')

SET @query = 'SELECT postcode, ' + @cols + ' FROM
                (
                    SELECT
                          postcode, address
                     FROM yourtable
               ) sourcedata
                pivot
                (
                     max([address])
                    FOR [address] IN (' + @cols + ')
                ) p '

--SELECT @query /* use select to inspect the generated sql */

-- EXECUTE(@query) /*once satisfied that sql is OK, use execute (INSTEAD) */

Open in new window

0
 
Ali ShahSQL DeveloperAuthor Commented:
Thanks a lot it helps
0
 
PortletPaulfreelancerCommented:
Great! Will you close off the question please?
0
 
PortletPaulfreelancerCommented:
Thank you. Closure of questions is really appreciated.

Cheers
Paul
0
 
Ali ShahSQL DeveloperAuthor Commented:
You are welcome Paul and really sorry i did not realise that i had not closed the question.

regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.