SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 issue with using pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/21/2012 :  09:28:38  Show Profile  Reply with Quote
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
2224 Posts

Posted - 09/21/2012 :  09:53:58  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/21/2012 :  09:54:12  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 09/21/2012 :  10:11:58  Show Profile  Reply with Quote
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
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/21/2012 :  11:11:01  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/21/2012 :  11:50:58  Show Profile  Reply with Quote
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
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/21/2012 :  15:49:51  Show Profile  Reply with Quote
Thanks Sunita,

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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/21/2012 :  15:56:34  Show Profile  Reply with Quote
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,
...
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/21/2012 :  16:05:42  Show Profile  Reply with Quote
Again Thanks Sunitabeck. That sorted out my problem.

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/10/2013 :  14:33:13  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000