Convert rows into columns

• hozefa_unwala 55218

  SSC Rookie

  Points: 29

  More actions

  November 13, 2014 at 2:22 pm

  #313021

  I have 5 columns in my database. 1 column is coming like a dynamic.

  I want to convert records from rows to columns. Currently I have a data like this.

  Race AgeRange Amount

  W 17-20 500

  W 21-30 400

  W 31-40 200

  A 17-20 100

  H 41-50 250

  H 51-60 290

  So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like

  Race 17-20 21-30 31-40 41-50 51-60

  W 500 400 200 0 0

  A 100 0 0 0 0

  H 0 0 0 250 290

  Thanks.

• g.britton

  SSChampion

  Points: 13863

  More actions

  November 13, 2014 at 2:31 pm

  #1758406

  DECLARE @t TABLE(Race char(1), AgeRange char(5), Amount int)

  INSERT INTO @t (Race, AgeRange, Amount) values

  ('W','17-20',5000),

  ('W','21-30',4000),

  ('W','31-40',2000),

  ('A','17-20',1000),

  ('H','41-50',2500),

  ('H','51-60',2900)

  select Race,

  isnull("17-20",0),

  isnull("21-30",0),

  isnull("31-40",0),

  isnull("41-50",0),

  isnull("51-60",0)

  from @t t

  pivot (max(Amount) for AgeRange in ("17-20", "21-30", "31-40", "41-50", "51-60") ) p

  Gerald Britton, Pluralsight courses

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 13, 2014 at 3:16 pm

  #1758411

  You could also try cross tabs. Read the following articles on how to work with them and how to make them dynamic.

  Part 1: http://www.sqlservercentral.com/articles/T-SQL/63681/

  Part 2: http://www.sqlservercentral.com/articles/Crosstab/65048/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  November 14, 2014 at 8:15 am

  #1758614

  Quick suggestion for a dynamic cross-tab solution

  

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;

  CREATE TABLE dbo.TBL_SAMPLE_DATA

  (

  Race CHAR(1) NOT NULL

  ,AgeRange CHAR(5) NOT NULL

  ,Amount NUMERIC(10,0) NOT NULL

  )

  INSERT INTO dbo.TBL_SAMPLE_DATA (Race, AgeRange, Amount)

  VALUES

  ('W','17-20',500)

  ,('W','21-30',400)

  ,('W','31-40',200)

  ,('A','17-20',100)

  ,('H','41-50',250)

  ,('H','51-60',290)

  ;

  DECLARE @SQL_STR NVARCHAR(MAX) = N'SELECT

  SD.Race

  {{COL_SET}}

  FROM dbo.TBL_SAMPLE_DATA SD

  GROUP BY SD.Race;';

  ;WITH COL_LIST(COLNAME) AS

  (

  SELECT DISTINCT

  SD.AgeRange AS COLNAME

  FROM dbo.TBL_SAMPLE_DATA SD

  )

  ,COL_SET AS

  (

  SELECT

  CL.COLNAME

  ,ROW_NUMBER() OVER

  (

  PARTITION BY (SELECT NULL)

  ORDER BY CL.COLNAME

  ) AS COL_POS

  FROM COL_LIST CL

  )

  SELECT @SQL_STR = REPLACE(@SQL_STR,N'{{COL_SET}}',(

  SELECT

  NCHAR(9) + N',ISNULL(MAX(CASE WHEN SD.AgeRange = '

  + NCHAR(39) + CS.COLNAME + NCHAR(39) + N' THEN SD.Amount END),0) AS '

  + QUOTENAME(CS.COLNAME) + NCHAR(13) + NCHAR(10)

  FROM COL_SET CS

  FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'));

  EXEC (@SQL_STR);

  Results

  Race 17-20 21-30 31-40 41-50 51-60

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

  A 100 0 0 0 0

  H 0 0 0 250 290

  W 500 400 200 0 0