Here is a solution based on ANSI SQL, which does not use the proprietery PIVOT operator. While it is somewhat more verbose, I find this model easier to understand. It is also a lot easier to extend and adapt to different needs.
SELECT ID, MIN(CASE AddrType WHEN 'Home' THEN HomeCity END) AS HomeCity, MIN(CASE AddrType WHEN 'Mailing' THEN HomeCity END) AS MailingCity FROM #Test GROUP BY ID
2 additional answers
Sort by: Most helpful
One of solutions is:
select ID, isnull(Home, '') as HomeCity, IsNull(Mailing, '') as MailingCity from #Test pivot ( max(HomeCity) for AddrType in ( Home, Mailing ) ) p order by ID
Hi @SQL ,
Glad that you already received your expected answer.
Please also refer blow dynamic pivot way from below:
declare @query nvarchar(max); declare @cols nvarchar(max); declare @cols1 nvarchar(max); select @cols = stuff((select ','+quotename(AddrType) from #Test group by AddrType for xml path('')), 1, 1, ''); select @cols1 = stuff((select ','+quotename(AddrType)+' '+AddrType+'City' from #Test group by AddrType for xml path('')), 1, 1, ''); set @query = 'select ID,'+@cols1+' from #Test pivot ( max(HomeCity) for AddrType in (' +@cols+ ') ) p order by ID' exec sp_executesql @query;
ID HomeCity MailingCity 10 Phoenix Boston 20 Memphis Durham 30 NULL New York
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
SQL Query Help
Need help with query. I want the output to be display in single row. We have 2 rows for each ID for Address Type and I want the Home and Mailing address city display in single row.
create table #Test (ID int, HomeCity varchar(10), AddrType varchar(10)) insert into #Test values (10,'Phoenix','Home') insert into #Test values (10,'Boston','Mailing') insert into #Test values (20,'Memphis','Home') insert into #Test values (20,'Durham','Mailing') insert into #Test values (30,'New York','Mailing') SELECT * from #Test
Sign in to comment