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
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
November 13, 2024 at 1:56 pm
Thanks a lot. That works.
November 13, 2024 at 5:33 pm
Thanks a lot. That works.
Just to be sure, do you understand WHY it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy