TSQL Pivot Crosstab
-
Hi,
I am doing a crosstab but I am unsure if its a pivot or is there a simpler way of doing it.
Create Table TblPerson (ID Int, Name Varchar(50), Child Varchar(50));
SELECT * FROM TblPerson ID, Name, Child
The output should look like this, not everyone has equal number of items so some condition fields will be blank!
Some people may have 1 or many conditions.
All help appreciated please.
Thank you.
- This topic was modified 10 months ago by Tallboy.
-
Deneyin.
DECLARE @TBLPERSON TABLE
(
ID INT,
NAME NVARCHAR(100),
CONDITION NVARCHAR(100)
)
INSERT INTO @TBLPERSON (ID,NAME,CONDITION)
VALUES ('1','Mary','DrySkin'),('2','Bob','DrySkin'),('3','Mary','Glasses'),('4','Bob','Glasses'),('5','Joe','Glasses'),('6','Mary','GreyHair'),('7','Bob','Glasses'),('8','Mary','Hearing'),('9','Joe','Hearing')
;WITH DETAY AS
(
select *,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ID) Sıra
from @TBLPERSON
)
SELECT NAME,(SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=1) AS CONDITION1,
(SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=2) AS CONDITION2,
(SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=3) AS CONDITION3,
(SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=4) AS CONDITION4
FROM DETAY
GROUP BY NAME
ORDER BY NAMEAllah bize yeter, O ne güzel vekildir.
vedatoozer@gmail.com
-
Hi Vedat,
That looks great thank you, I will try it out, but is it limited to 4 conditions? as there could be many more per person!
-
;WITH Cte_Persons AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Id) AS Row_Num
FROM @TBLPERSON
)
SELECT
Name,
MAX(CASE WHEN row_num = 1 THEN Condition END) AS Condition1,
MAX(CASE WHEN row_num = 2 THEN Condition END) AS Condition2,
MAX(CASE WHEN row_num = 3 THEN Condition END) AS Condition3,
MAX(CASE WHEN row_num = 4 THEN Condition END) AS Condition4,
MAX(CASE WHEN row_num = 5 THEN Condition END) AS Condition5,
MAX(CASE WHEN row_num = 6 THEN Condition END) AS Condition6,
MAX(CASE WHEN row_num = 7 THEN Condition END) AS Condition7,
MAX(CASE WHEN row_num = 8 THEN Condition END) AS Condition8,
/* ... */
FROM Cte_Persons
GROUP BY NameSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
ScottPletcher very nice solution
Allah bize yeter, O ne güzel vekildir.
vedatoozer@gmail.com
-
Looks great Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply