Change table from vertical to horizontal

• benkraiemchedlia

  SSCommitted

  Points: 1672

  More actions

  November 30, 2016 at 5:50 am

  #401135

  Hi,

  I have a table "Questions" with 2 columns "id", "text" it already contains many rows (200 rows):

  id | text

  1 | aa

  2 | bb

  3 | cc

  ...

  I want to change the display form to this:

  id | 1 | 2 | 3

  text | aa | bb | cc

  is there a way to perform this ?

  thanks

• Jo Pattyn

  SSC-Dedicated

  Points: 32350

  More actions

  November 30, 2016 at 5:55 am

  #1915140

  try using pivot

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  November 30, 2016 at 7:23 am

  #1915160

  Quick suggestion

  😎

  USE TEEST;

  GO

  SET NOCOUNT ON;

  ;WITH SAMPLE_DATA(SD_ID,SD_TEXT) AS

  (

  SELECT 1,'AA' UNION ALL

  SELECT 2,'BB' UNION ALL

  SELECT 3,'CC'

  )

  SELECT

  'ID' AS COLNAME

  ,CONVERT(VARCHAR(50),MAX(CASE WHEN SD.SD_ID = 1 THEN SD.SD_ID END),0) AS COL01

  ,CONVERT(VARCHAR(50),MAX(CASE WHEN SD.SD_ID = 2 THEN SD.SD_ID END),0) AS COL02

  ,CONVERT(VARCHAR(50),MAX(CASE WHEN SD.SD_ID = 3 THEN SD.SD_ID END),0) AS COL03

  FROM SAMPLE_DATA SD

  UNION ALL

  SELECT

  'TEXT' AS COLNAME

  ,MAX(CASE WHEN SD.SD_ID = 1 THEN SD.SD_TEXT END)

  ,MAX(CASE WHEN SD.SD_ID = 2 THEN SD.SD_TEXT END)

  ,MAX(CASE WHEN SD.SD_ID = 3 THEN SD.SD_TEXT END)

  FROM SAMPLE_DATA SD;

  Output

  COLNAME COL01 COL02 COL03

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

  ID 1 2 3

  TEXT AA BB CC

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  November 30, 2016 at 8:21 am

  #1915178

  benkraiemchedlia (11/30/2016)

  ---

  Hi,

  I have a table "Questions" with 2 columns "id", "text" it already contains many rows (200 rows):

  id | text

  1 | aa

  2 | bb

  3 | cc

  ...

  I want to change the display form to this:

  id | 1 | 2 | 3

  text | aa | bb | cc

  is there a way to perform this ?

  thanks

  There are many ways to do this but, as of right now, you're going to end up with a return of 2 rows each containing 200 columns. My question is... what on this good green Earth are you going to do with that result once it's formed? If we knew the business logic that has made you or someone else think it needs to be done this way, we might be able to suggest a much more effective alternative because this way is generally going to lead to nothing but problems.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eric M Russell

  SSC Guru

  Points: 125562

  More actions

  November 30, 2016 at 8:41 am

  #1915187

  Whatever user requested to see the data pivoted into this cross-tab format should do it themselves within the reporting tool.

  Create a Matrix (Report Builder and SSRS)

  https://msdn.microsoft.com/en-us/library/dd207149.aspx

  Office Excel 2010 Tutorial: Pivot Tables

  http://www.addictivetips.com/windows-tips/office-excel-2010-tutorial-pivot-tables/

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho