Transpose columns to Rows ( Without any function)

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  August 17, 2007 at 7:24 am

  #178122

  Hi ,

  I need to transpose the column name to row( without using any function) ,can anybody help me to sort out this problem ?

  Example :

   select sum(ParAmt )ParAmt ,   

   sum(DenomQty )DenomQty ,   

   sum(convert(float,CInsNbr))CInsNbr,   

   sum(convert(float,HNbr))HNbr   

  from CHold

  output :

  ParAmt       DenomQty  CInsNbr         HNbr

  92376.9341 121.0         131852658.0  2281.0

  But i like to display the output as

  ParAmt      92376.9341

  DenomQty  121.0

  CInsNbr      131852658.0

  HNbr          2281.0   

  ( Note : I dont want to use any system or predefined functions.

  I need a pure sql code to complete this issue )

   

  Regards

  Karthik

   

   

   

  karthik

• Ken McKelvey

  SSCoach

  Points: 18853

  More actions

  August 17, 2007 at 7:51 am

  #727642

  SELECT

      CASE N.Number

          WHEN 1 THEN 'ParAmt'

          WHEN 2 THEN 'DenomQty'

          WHEN 3 THEN 'CInsNbr'

          WHEN 4 THEN 'HNbr'

      END AS Col

      ,CASE N.Number

          WHEN 1 THEN ParAmt -- cast to float if not already float

          WHEN 2 THEN DenomQty -- cast to float if not already float

          WHEN 3 THEN CInsNbr

          WHEN 4 THEN HNbr

      END AS Value

  FROM (

          SELECT SUM(ParAmt ) AS ParAmt

              ,SUM(DenomQty ) AS DenomQty

              ,SUM(CAST(CInsNbr AS float)) AS CInsNbr

              ,SUM(CAST(HNbr AS float)) AS HNbr

          FROM CHold

      ) D

      CROSS JOIN (

              SELECT 1 UNION ALL

              SELECT 2 UNION ALL

              SELECT 3 UNION ALL

              SELECT 4

          ) N (Number)

   

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  August 17, 2007 at 9:16 am

  #727675

  Hi Karthik,

  UNPIVOT can do what you need. The query would look like:

  SELECT PropertyID, data

  FROM ( SELECT

  CONVERT(FLOAT,SUM(ParAmt)) ParAmt

  , CONVERT(FLOAT,SUM(DenomQty)) DenomQty

  , SUM(CONVERT(FLOAT, CInsNbr)) CInsNbr

  , SUM(CONVERT(FLOAT, HNbr)) HNbr

  FROM CHold

  ) AS SourceTable UNPIVOT ( data FOR PropertyID IN

  ( [ParAmt], [DenomQty], [CInsNbr], [HNbr] ) ) AS PivotTable

  Note that I cast the int columns to FLOAT, to make sure that the result column will have a single data type.

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  August 17, 2007 at 9:20 am

  #727678

  Just to add to my solution: It works on SQL Server 2005 only. You can read more about pivot and unpivot on

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/24ba54fc-98f7-4d35-8881-b5158aac1d66.htm

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  August 17, 2007 at 11:00 am

  #727708

  Hi Ken,

   

  I am getting only 'NULL' value in the Value Column.

   

  Output :

   

  Col              Value                                                

  ---------------- -----------------------------------------------------

  ParAmt      NULL

  DenomQty  NULL

  CINbr        NULL

  HNbr         NULL

  (4 row(s) affected)

   

   

  karthik