How to display data to row wise in sql?

Question

Select * from Table1

output

Alias(Auto generate no)   ShortName       LongName         IssuerID       TypeName
 1                         ABC           ABC Pvt Ltd.         23            Current
 2                         DEF           DEF Pvt Ltd.         34            Provisional
 3                         GHI           GHI Pvt Ltd.         50            Legacy

I want to make in row wise view

Expected Output

Fields        Current           Provisional      Legacy

Alias          1                   2              3
ShortName      ABC                DEF            GHI
LongName       ABC Pvt Ltd.      DEF Pvt Ltd.   GHI Pvt Ltd.
IssuerID       23                 34              50

How do I make a select query for the above condition?

I have tried:

select *
from Table1
pivot
(
   avg(IssuerID) for TypeName in
   (
      [Fields],[Current],[Provisional],[Legacy]
   )
) as TypeName

What have you tried? You'll want to PIVOT the data. A quick Google search will send you in the right direction. — Adam Wenger, Nov 20 '12 at 03:51
select * from Table1 pivot (avg(IssuerID) for TypeName in ([Fields],[Current],[Provisional],[Legacy])) as TypeName This is not working!... — Mohan Raj, Nov 20 '12 at 04:24
@AdamWenger Care to show an example of PIVOT where the column names become values? — Conrad Frix, Nov 20 '12 at 05:05

Taryn · Answer 1 · 2012-11-20T10:21:48.627

This type of data transformation can be done by applying both the UNPIVOT and then the PIVOT functions in SQL Server.

The UNPIVOT function takes your columns Alias, ShortName, LongName and IssuerID and converts them to row values. However, in order for the UNPIVOT to work the datatypes for these values must be the same:

select typename, value, fields
from
(
  select cast(alias as varchar(20)) alias,
    shortname,
    longname,
    cast(issuerid as varchar(20)) issuerid,
    typename
  from Table1
) u
unpivot
(
  value
  for fields in (Alias, ShortName, LongName, IssuerId)
) unpiv

See SQL Fiddle with Demo

Once the data has been UNPIVOTed, then you can apply the PIVOT function to the Typename column values:

select fields, [current], [provisional], [legacy]
from
(
  select typename, value, fields
  from
  (
    select cast(alias as varchar(20)) alias,
      shortname,
      longname,
      cast(issuerid as varchar(20)) issuerid,
      typename
    from Table1
  ) u
  unpivot
  (
    value
    for fields in (Alias, ShortName, LongName, IssuerId)
  ) unpiv
) src
pivot
(
  max(value)
  for typename in([current], [provisional], [legacy])
) piv

See SQL Fiddle with Demo

The result of the query is:

|    FIELDS |      CURRENT |  PROVISIONAL |       LEGACY |
----------------------------------------------------------
|     alias |            1 |            2 |            3 |
|  issuerid |           23 |           34 |           50 |
|  longname | ABC Pvt Ltd. | DEF Pvt Ltd. | GHI Pvt Ltd. |
| shortname |          ABC |          DEF |          GHI |

If you do not have access to the UNPIVOT and PIVOT functions, then you can use a UNION ALL query to replicate the UNPIVOT and then an aggregate function with a CASE to replicate a PIVOT:

select fields,
  max(case when typename = 'current' then value end) [current],
  max(case when typename = 'provisional' then value end) provisional,
  max(case when typename = 'legacy' then value end) legacy
from
(
  select typename, cast(alias as varchar(20)) value, 'alias' fields
  from Table1 
  union all
  select typename, shortname value, 'shortname' fields
  from Table1 
  union all
  select typename, longname value, 'longname' fields
  from Table1 
  union all
  select typename, cast(issuerid as varchar(20)) value, 'issuerid' fields
  from Table1 
) src
group by fields

See SQL Fiddle with Demo

The result will be the same with both versions.

score 0 · Answer 2 · edited May 23 '17 at 12:27

What you need, is not simple and it is not a good task for PIVOT.

Here is a similiar question and a possible solution for these kind of transmission.

First of all, if you check your wanted output, you even have to "mix" the types of the columns. So for this, for sure you have to cast or convert the integer or other number columns to varchar types too. (A column has only one type)

And for PIVOT, you can use only one aggregate. Sometimes it simply to much hadache to think about how to trick it, sometimes an easier but longer solution could work better.

You can try a code like this: (Here is an SQL Fiddle demo to show it.)

WITH CTE_DATA
AS (
  SELECT  DISTINCT
  CAST([Current].Alias as varchar(64)) AS Alias_Current,
  [Current].ShortName AS ShortName_Current,
  [Current].LongName AS LongName_Current,
  CAST([Current].IssuerID as varchar(64)) AS IssuerID_Current,
  CAST(Provisional.Alias as varchar(64)) AS Alias_Provisional,
  Provisional.ShortName AS ShortName_Provisional,
  Provisional.LongName AS LongName_Provisional,
  CAST(Provisional.IssuerID as varchar(64)) AS IssuerID_Provisional,
  CAST(Legacy.Alias as varchar(64)) AS Alias_Legacy,
  Legacy.ShortName AS ShortName_Legacy,
  Legacy.LongName AS LongName_Legacy,
  CAST(Legacy.IssuerID as varchar(64)) AS IssuerID_Legacy
  FROM 
  (SELECT * FROM Data WHERE TypeName = 'Current') As [Current]
  CROSS JOIN (SELECT * FROM Data WHERE TypeName = 'Provisional') As Provisional
  CROSS JOIN (SELECT * FROM Data WHERE TypeName = 'Legacy') AS Legacy
  )
SELECT 'Alias' AS Fields, Alias_Current AS [Current], 
Alias_Provisional AS Provisional, Alias_Legacy AS Legacy
    FROM CTE_DATA
    UNION ALL
SELECT 'ShortName' AS Fields, ShortName_Current AS [Current], 
ShortName_Provisional AS Provisional, ShortName_Legacy AS Legacy
    FROM CTE_DATA
    UNION ALL
SELECT 'LongName' AS Fields, LongName_Current AS [Current], 
LongName_Provisional AS Provisional, LongName_Legacy AS Legacy
    FROM CTE_DATA
    UNION ALL
SELECT 'IssuerID' AS Fields, IssuerID_Current AS [Current], 
IssuerID_Provisional AS Provisional, IssuerID_Legacy AS Legacy
    FROM CTE_DATA

How to display data to row wise in sql?

2 Answers2