July 22, 2016 at 9:49 am
Hello,
I am a bit lost in regards of pivoting data. Is it possible to pivot a data-set without an aggregate?
Below is an example. The amount of columns in the output will always be 10 columns.
CREATE TABLE #TEST
(NAME VARCHAR(50), VALUE VARCHAR(50))
GO
INSERT INTO #TEST VALUES('JOHN','AAA')
INSERT INTO #TEST VALUES('JOHN','BBB')
INSERT INTO #TEST VALUES('JOHN','CCC')
INSERT INTO #TEST VALUES('JOHN','DDD')
INSERT INTO #TEST VALUES('JOHN','EEE')
INSERT INTO #TEST VALUES('JOHN','FFF')
INSERT INTO #TEST VALUES('JOHN','GGG')
INSERT INTO #TEST VALUES('BILL','AAA')
INSERT INTO #TEST VALUES('BILL','BBB')
INSERT INTO #TEST VALUES('GLEN','AAA')
INSERT INTO #TEST VALUES('STACY','AAA')
INSERT INTO #TEST VALUES('LU','AAA')
INSERT INTO #TEST VALUES('LU','AAA')
GO
The output should look like this:
JOHN, AAA, BBB, CCC, DDD, EEE, FFF, GGG, NULL, NULL, NULL
BILL, AAA, BBB, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
GLEN, AAA, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
STACY, AAA, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
LU, AAA, AAA, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
July 22, 2016 at 10:01 am
Think of it like this: you have to take data from all 7 rows for John, but somehow only return 1 row with data from all of them. That's an aggregate by definition.
What exactly are you trying to do? Have you looked at the old-fashioned but faster and more flexible CROSSTAB query?
imba (7/22/2016)
Hello,I am a bit lost in regards of pivoting data. Is it possible to pivot a data-set without an aggregate?
Below is an example. The amount of columns in the output will always be 10 columns.
CREATE TABLE #TEST
(NAME VARCHAR(50), VALUE VARCHAR(50))
GO
INSERT INTO #TEST VALUES('JOHN','AAA')
INSERT INTO #TEST VALUES('JOHN','BBB')
INSERT INTO #TEST VALUES('JOHN','CCC')
INSERT INTO #TEST VALUES('JOHN','DDD')
INSERT INTO #TEST VALUES('JOHN','EEE')
INSERT INTO #TEST VALUES('JOHN','FFF')
INSERT INTO #TEST VALUES('JOHN','GGG')
INSERT INTO #TEST VALUES('BILL','AAA')
INSERT INTO #TEST VALUES('BILL','BBB')
INSERT INTO #TEST VALUES('GLEN','AAA')
INSERT INTO #TEST VALUES('STACY','AAA')
INSERT INTO #TEST VALUES('LU','AAA')
INSERT INTO #TEST VALUES('LU','AAA')
GO
The output should look like this:
JOHN, AAA, BBB, CCC, DDD, EEE, FFF, GGG, NULL, NULL, NULL
BILL, AAA, BBB, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
GLEN, AAA, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
STACY, AAA, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
LU, AAA, AAA, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2016 at 10:02 am
To my knowledge there is no way to do what you want without the use of an Aggregate.
Does the column order matter?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
July 22, 2016 at 10:11 am
maybe ??
WITH cte AS (
SELECT NAME,
VALUE,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY (SELECT NULL)) rn
FROM #TEST
)
SELECT NAME,
MAX(CASE WHEN rn = 1 THEN VALUE END) as c1,
MAX(CASE WHEN rn = 2 THEN VALUE END) as c2,
MAX(CASE WHEN rn = 3 THEN VALUE END) as c3
FROM cte
GROUP BY NAME
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 22, 2016 at 10:11 am
Column order does not
July 22, 2016 at 10:18 am
J Livingston, Thank you so much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply