issue with using pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

issue with using pivot

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-21 : 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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-21 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-21 : 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

230 Posts

Posted - 2012-09-21 : 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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 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

230 Posts

Posted - 2012-09-21 : 15:49:51

Thanks Sunita,

It works now......
But is it possible to make the null value as blank in output?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 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

230 Posts

Posted - 2012-09-21 : 16:05:42

Again Thanks Sunitabeck. That sorted out my problem.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-10 : 14:33:13

Hi Sunita,

Just today I was trying to understand your query how it works and giving correct result. In the process, I replaced your row_over with "id" column and it produced some strange output. I wonder what is happening with the query when I replace with "id" column exactly. Can you please it to me if possible?



	SELECT personid, category,
	id AS RN
	FROM @tab
	group by personid, category

quote:

Originally posted by sunitabeck

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources