SQL server help (converting rows to columns)

Question

Post reply

SQL server help (converting rows to columns)

don075

SSC-Addicted

Points: 421
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: 24386

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

don075 SSC-Addicted Points: 421 More actions · Answer 1

don075

SSC-Addicted

Points: 421

November 13, 2024 at 1:56 pm

#4481140

Thanks a lot. That works.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)