| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
155 Posts |
 Posted - 09/21/2012 : 09:28:38
|
I have a table as below
personid | date | category | column4 | column5 | column6 | column7
17894 |23 may 12 | A | values | values | values | values
17894 |23 may 12 | A | values | values | values | values
17897 |24 May 12 | B | values | values | values | values
17896 |24 may 12 | B | values | values | values | values
27895 |24 may 12 | C | values | values | values | values
27898 |24 May 12 | C | values | values | values | values
27899 |23 may 12 | C | values | values | values | values
37891 |23 may 12 | D | values | values | values | values
37845 |24 May 12 | D | values | values | values | values
37845 |24 May 12 | D | values | values | values | values
37836 |24 may 12 | D | values | values | values | values
47816 |24 may 12 | D | values | values | values | values
47816 |24 may 12 | D | values | values | values | values
47826 |24 May 12 | D | values | values | values | values
I want output as below : (basically distinct personid for each category)
CategoryA | CategoryB | CategoryC | CategoryD
17894 | 17897 | 27895 | 47816
| 17896 | 27898 | 47826
| | 27899 |
I tried the code below
select A as CategoryA, B as CategoryB, C as CategoryC, D as CategoryD
from
(
select personid, category
from table1
group by personid, category1
) as T
PIVOT
(
max(personid)
FOR category in (A, B, C, D)
)
as PVT
This gives only the following output
CategoryA | CategoryB | CategoryC | CategoryD
17894 | 17897 | 27899 | 47826
|
Edited by - learning_grsql on 09/21/2012 09:47:16
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 09/21/2012 : 09:53:58
|
Hi,
Try this code
DECLARE @tab TABLE (personid int, date datetime2, category varchar(10), column4 varchar(10), column5 varchar(10),column6 varchar(10), column7 varchar(10))
INSERT INTO @tab VALUES(17894, '12-05-23', 'A', 'values', 'values', 'values', 'values'),
(17894, '12-05-23', 'A', 'values', 'values', 'values', 'values'),
(17897, '12-05-24', 'B', 'values', 'values', 'values', 'values'),
(17896, '12-05-24', 'B', 'values', 'values', 'values', 'values'),
(27895, '12-05-24', 'C', 'values', 'values', 'values', 'values'),
(27898, '12-05-24', 'C', 'values', 'values', 'values', 'values'),
(27899, '12-05-23', 'C', 'values', 'values', 'values', 'values'),
(37891, '12-05-23', 'D', 'values', 'values', 'values', 'values'),
(37845, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(37845, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(37836, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(47816, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(47816, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(47826, '12-05-24', 'D', 'values', 'values', 'values', 'values')
SELECT distinct Case category when 'A' Then cast(personid as varchar(10)) end as CategoryA,
Case category when 'B' Then cast(personid as varchar(10)) end as CategoryB,
Case category when 'C' Then cast(personid as varchar(10)) end as CategoryC,
Case category when 'D' Then cast(personid as varchar(10)) end as CategoryD
From @tab
--
Chandu |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/21/2012 : 09:54:12
|
What is the logic for omitting 37845, 37846 from D? In any case, see if this will get you close to what you need:;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY personid) AS RN
FROM table1
group by personid, category1
)
SELECT A AS CategoryA,
B AS CategoryB,
C AS CategoryC,
D AS CategoryD
FROM cte
PIVOT(MAX(personid) FOR category IN (A, B, C, D)) AS PVT |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 09/21/2012 : 10:11:58
|
quote:
Originally posted by sunitabeck
What is the logic for omitting 37845, 37846 from D? In any case, see if this will get you close to what you need:;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY personid) AS RN
FROM table1
group by personid, category1
)
SELECT A AS CategoryA,
B AS CategoryB,
C AS CategoryC,
D AS CategoryD
FROM cte
PIVOT(MAX(personid) FOR category IN (A, B, C, D)) AS PVT
Try this one sunitabeck.....
;WITH cte AS
(
SELECT *,
Rank() OVER (PARTITION BY category ORDER BY personid) AS RN
FROM @tab
)
SELECT A AS CategoryA,
B AS CategoryB,
C AS CategoryC,
D AS CategoryD
FROM cte
PIVOT(MAX(personid) FOR category IN (A, B, C, D)) AS PVT
--
Chandu |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 09/21/2012 : 11:11:01
|
Thanks @Sunitabeck and @bandi
@sunitabeck
Sorry it was my typo. It should have included those two values also in D column.
I just tried your code and bandi(second) code but I get blank result.(0 rows affected).
@bandi
As to your first code, I want to use pivot to get the result. I used to do that way(case when) before but it's lengthy as data is getting bigger now
|
Edited by - learning_grsql on 09/21/2012 11:12:47 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/21/2012 : 11:50:58
|
Borrowing Bandi's test table and data, the query I posted (with fixes and typo corrects) seems to give close to what you are looking for:-- bandi's awesome test data.
DECLARE @tab TABLE (personid int, date datetime2, category varchar(10), column4 varchar(10), column5 varchar(10),column6 varchar(10), column7 varchar(10))
INSERT INTO @tab VALUES(17894, '12-05-23', 'A', 'values', 'values', 'values', 'values'),
(17894, '12-05-23', 'A', 'values', 'values', 'values', 'values'),
(17897, '12-05-24', 'B', 'values', 'values', 'values', 'values'),
(17896, '12-05-24', 'B', 'values', 'values', 'values', 'values'),
(27895, '12-05-24', 'C', 'values', 'values', 'values', 'values'),
(27898, '12-05-24', 'C', 'values', 'values', 'values', 'values'),
(27899, '12-05-23', 'C', 'values', 'values', 'values', 'values'),
(37891, '12-05-23', 'D', 'values', 'values', 'values', 'values'),
(37845, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(37845, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(37836, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(47816, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(47816, '12-05-24', 'D', 'values', 'values', 'values', 'values'),
(47826, '12-05-24', 'D', 'values', 'values', 'values', 'values')
-- sunita's even awesomer query
;WITH cte AS
(
SELECT personid, category,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY personid) AS RN
FROM @tab
group by personid, category
)
SELECT A AS CategoryA,
B AS CategoryB,
C AS CategoryC,
D AS CategoryD
FROM cte
PIVOT(MAX(personid) FOR category IN (A, B, C, D)) AS PVT
-- CategoryA CategoryB CategoryC CategoryD
-- 17894 17896 27895 37836
-- NULL 17897 27898 37845
-- NULL NULL 27899 37891
-- NULL NULL NULL 47816
-- NULL NULL NULL 47826 |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 09/21/2012 : 15:49:51
|
Thanks Sunita,
It works now......
But is it possible to make the null value as blank in output? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/21/2012 : 15:56:34
|
quote:
Originally posted by learning_grsql
Thanks Sunita,
It works now......
But is it possible to make the null value as blank in output?
You can, but you will need to cast the numbers to strings to do that. If at all possible, that is best done at the presentation layer (if in-fact you have a presentation layer). Otherwise, do it like this:...
ISNULL(CAST(A AS VARCHAR(32)),'') AS CategoryA,
... |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 09/21/2012 : 16:05:42
|
Again Thanks Sunitabeck. That sorted out my problem.
|
|
|
| |
Topic |
|
|
|
issue with using pivot
