How to Get the Output in a row instead of column Grid?

• adc22

  Old Hand

  Points: 338

  More actions

  June 9, 2012 at 12:43 pm

  #262252

  Hi All,

  I have DEPT table like below,

  DeptNo DNAME

  10ACCOUNTING

  20RESEARCH

  30SALES

  40OPERATIONS

  I Need to get a output (report) like below ( 2 Rows).

  ACCOUNTINGRESEARCHSALESOPERATIONS

  10 2030 40

  Please help. Thanks in advance.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 9, 2012 at 1:10 pm

  #1499049

  What will be column names for the 2 rows?

• vinu512

  SSCoach

  Points: 15729

  More actions

  June 10, 2012 at 11:00 pm

  #1499137

  You can use any of the following variations. The Dynamic Pivot would be my pick.

  --Creating Tables

  Create Table Ex

  (DeptNo int,

  DNAME varchar(20) )

  --Inserting Sample Data

  Insert Into Ex

  Select 10,'ACCOUNTING'

  Union ALL

  Select 20,'RESEARCH'

  Union ALL

  Select 30,'SALES'

  Union ALL

  Select 40,'OPERATIONS'

  --Query Using Case

  Select MAX(Accounting) As Accounting, MAX(Research) As Research, MAX(Sales) As Sales, MAX(Operations) As Operations From

  (Select DeptNo,

  (Case When DNAME = 'Accounting' Then DeptNo Else '' End) As Accounting,

  (Case When DNAME = 'Research' Then DeptNo Else '' End) As Research,

  (Case When DNAME = 'Sales' Then DeptNo Else '' End) As Sales,

  (Case When DNAME = 'Operations' Then DeptNo Else '' End) As Operations

  From Ex) As a

  --Static Pivot

  Select [Accounting], [Research], [Sales], [Operations] From Ex

  Pivot

  (MAX(DeptNo) For DName IN ([Accounting], [Research], [Sales], [Operations])) As pvt

  --Dynamic Pivot

  Declare @cols varchar(max), @sql varchar(max)

  Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(DName) From Ex

  Set @sql = 'Select '+@cols+' From Ex

  Pivot

  (MAX(DeptNo) For DName IN ('+@cols+')) As pvt'

  Execute (@sql)

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉