In SQL Server, how to show table rows value in column?

Question

I'm working with SQL Server 2012, my table looks like this:

Customer    Status         StatusType
----------------------------------------
  3         Ok             personalInfo
  3         Pending        FinancialInfo
  3         NeedUpdate     CompanyInfo

To get the result here, I need a SQL query:

Customer    PersonalInfo    FinancialInfo   CompanyInfo
---------------------------------------------------------
    3       Ok              Pending         NeedUpdate

Simply you need `Pivot` or query to convert the row to column.That's it. — AT-2017, Feb 01 '17 at 06:21

Chanukya · Accepted Answer · 2017-02-01T10:14:34.157

    CREATE TABLE #Table1
    ([Customer] int, [Status] varchar(10), [StatusType] varchar(13))
;

INSERT INTO #Table1
    ([Customer], [Status], [StatusType])
VALUES
    (3, 'Ok', 'personalInfo'),
    (3, 'Pending', 'FinancialInfo'),
    (3, 'NeedUpdate', 'CompanyInfo')      
SELECT * 
    FROM #Table1
    PIVOT ( max([status])
      for [StatusType] in ([PersonalInfo], [FinancialInfo], [CompanyInfo])) AS pvt

output

Customer    PersonalInfo    FinancialInfo   CompanyInfo
3             Ok             Pending         NeedUpdate

or

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[StatusType]) 
            FROM #Table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT customer, ' + @cols + ' from 

                #Table1
    PIVOT ( max([status])
      for [StatusType] in ([PersonalInfo], [FinancialInfo], [CompanyInfo])) AS pvt'


exec(@query)

there is one problem, Pivot in operator,I StatusType are not fixed,they are dynamic — shamim, Feb 01 '17 at 06:44

In SQL Server, how to show table rows value in column?

1 Answers1