SQL server help (converting rows to columns)

• don075

  SSC-Addicted

  Points: 429

  More actions

  November 13, 2024 at 2:04 am

  Go to Answer

  #4480915

  I am using the following sql to convert rows into columns for each pid, intdate. This works fine when f1,intdate and docdate are not changed.

  So with the following sample data, I get 2 rows.

  INSERT INTO #Data (pid,f1,mne,mneval,intdate,docdate) VALUES

  ('u1','11','Q1', '1','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q2', 'Y','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q3', 'N','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q4', '2','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q5', '3','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q6', '4','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q1', '5','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q4', '6','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q5', '7','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q6', '8','01/01/2000','01/01/2000 10:30'),

  ('u2','11','Q2', 'VV','01/01/2000','01/01/2000 10:30'),

  ('u2','11','Q7', 'A','01/01/2000','01/01/2000 10:30')

  I then changed the f1 field in the last row to 'aaa'. Now I am getting 3 rows.

  I only need to get 2 rows. Is this possible to do?

  INSERT INTO #Data (pid,f1,mne,mneval,intdate,docdate) VALUES

  ('u1','11','Q1', '1','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q2', 'Y','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q3', 'N','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q4', '2','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q5', '3','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q6', '4','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q1', '5','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q4', '6','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q5', '7','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q6', '8','01/01/2000','01/01/2000 10:30'),

  ('u2','11','Q2', 'VV','01/01/2000','01/01/2000 10:30'),

  ('u2','aaa','Q7', 'A','01/01/2000','01/01/2000 10:30')

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

  Complete sql

  CREATE TABLE #Data (

  pid VARCHAR(50) NOT NULL,

  f1 VARCHAR(50) NOT NULL,

  mne VARCHAR(50) NOT NULL,

  mneval VARCHAR(50) NOT NULL,

  intdate date,

  docdate datetime

  )

  INSERT INTO #Data (pid,f1,mne,mneval,intdate,docdate) VALUES

  ('u1','11','Q1', '1','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q2', 'Y','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q3', 'N','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q4', '2','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q5', '3','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q6', '4','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q1', '5','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q4', '6','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q5', '7','01/01/2000','01/01/2000 10:30'),

  ('u1','11','Q6', '8','01/01/2000','01/01/2000 10:30'),

  ('u2','11','Q2', 'VV','01/01/2000','01/01/2000 10:30'),

  ('u2','aaa','Q7', 'A','01/01/2000','01/01/2000 10:30')

  select [pid]

  [Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7]

  from #Data

  pivot(max([mneval])

  for [mne] in ([Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7])) as P

  drop table #Data

• DesNorton

  SSC-Insane

  Points: 24512

  More actions

  November 13, 2024 at 12:59 pm

  Answer

  #4481105

  You can write this as a cross-tab query

  SELECT  Q1 = pid
        , Q2 = MAX(IIF(mne = 'Q2', mneval, null))
        , Q3 = MAX(IIF(mne = 'Q3', mneval, null))
        , Q4 = MAX(IIF(mne = 'Q4', mneval, null))
        , Q5 = MAX(IIF(mne = 'Q5', mneval, null))
        , Q6 = MAX(IIF(mne = 'Q6', mneval, null))
        , Q7 = MAX(IIF(mne = 'Q7', mneval, null))
  FROM    #Data
  GROUP BY pid
  

  ---

  How to post data/code on a forum to get the best help.

• don075

  SSC-Addicted

  Points: 429

  More actions

  November 13, 2024 at 1:56 pm

  #4481140

  Thanks a lot. That works.

• Jeff Moden

  SSC Guru

  Points: 1004600

  More actions

  November 13, 2024 at 5:33 pm

  #4481259

  don075 wrote:

  Thanks a lot. That works.

   

  Just to be sure, do you understand WHY it works?

  --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)