Help with Grouping Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Grouping Query

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-04-06 : 09:22:55

I'm trying to get the following output to work with no luck - please help


input data is - DDL is below - 
	
Category   CType	Locaion
Mats	   2	        ZoneA
Mats	   3	        ZoneC
Pulleys	   1	        ZoneA
Pulleys	   2	        ZoneA
Widgets	   1	        ZoneX
		
please help with the needed output below - 
';' could be commas or pipes ('|') ... 

Widgets	   CType	Loction
Mats	   2;3	        ZoneA;ZoneC
Pulleys	   1;2	        ZoneA
Widgets	   1	        ZoneX

DDL is -

declare @TableTest table (
Category varchar(20) NOT NULL,
CType nvarchar(20) NOT NULL,
Location varchar(20) NOT NULL )

Insert Into @TableTest (Category,CType,Location)
select 'Widgets',1,'ZoneX'
union
select 'Pulleys',2,'ZoneA'
union
select 'Pulleys',1,'ZoneA'
union
select 'Mats',3,'ZoneC'
union
select 'Mats',2,'ZoneA'

select * from @TableTest

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-06 : 09:43:26

[code]
declare @TableTest table (
Category varchar(20) NOT NULL,
CType nvarchar(20) NOT NULL,
Location varchar(20) NOT NULL )

Insert Into @TableTest (Category,CType,Location)
select 'Widgets',1,'ZoneX'
union
select 'Pulleys',2,'ZoneA'
union
select 'Pulleys',1,'ZoneA'
union
select 'Mats',3,'ZoneC'
union
select 'Mats',2,'ZoneA'

--select * from @TableTest

;with cte as
(
SELECT Category, CType, Location,
ROW_NUMBER() OVER(Partition by Category order by Category) seq
FROM @TableTest a
)
SELECT Category,
STUFF((SELECT ':' + Ctype FROM cte WHERE Category = c.Category FOR XML PATH('')), 1,1,''),
STUFF((SELECT ',' + Location FROM cte WHERE Category = c.Category FOR XML PATH('')), 1,1,'')
FROM cte c
WHERE seq = 1
[/code]

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-04-06 : 11:24:15

Bad SQL Programmers always ask how to destroy 1NF. An expensive recursive CTE is the best of the bad answers. The right answer is to do display formatting in the front end and not in the database. Do you have a report writer?

--CELKO--
Joe Celko, SQL Guru

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 11:42:45

quote:
Originally posted by jcelko

Bad SQL Programmers always ask how to destroy 1NF. An expensive recursive CTE is the best of the bad answers. The right answer is to do display formatting in the front end and not in the database. Do you have a report writer?

--CELKO--
Joe Celko, SQL Guru

that depends on where you're showing the data. Sometime you do have to retrieve data as in format shown from table when you dont have any easier way to achieve this at your front end.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-04-06 : 12:01:15

no jcelko I don' have a report writer -

and thank you Vaibhav

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-04-06 : 17:04:47

Sorry Vaibhav your output for Pulley looks like

Pulleys 1:2 ZoneA,ZoneA

i need it to read

Pulleys 1:2 ZoneA

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 00:14:02

quote:
Originally posted by dpais

Sorry Vaibhav your output for Pulley looks like

Pulleys 1:2 ZoneA,ZoneA

i need it to read

Pulleys 1:2 ZoneA

thanks

make a small tweak as follows and try


;with cte as
(
SELECT Category, CType, Location,
ROW_NUMBER() OVER(Partition by Category order by Category) seq
FROM @TableTest a
)
SELECT	Category, 
		STUFF((SELECT ':' + Ctype FROM cte WHERE Category = c.Category FOR XML PATH('')), 1,1,''),
		STUFF((SELECT DISTINCT ',' + Location FROM cte WHERE Category = c.Category FOR XML PATH('')), 1,1,'')
FROM cte c
WHERE seq = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-07 : 02:35:34

Hi visakh

I have seen that output of my query is not giving required output.
but i know there i need to put distinct somewhere
but i was nt able to find the place

Thats why i posted the query as it is to get the advice from the experts like you...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 04:02:33

quote:
Originally posted by vaibhavktiwari83

Hi visakh

I have seen that output of my query is not giving required output.
but i know there i need to put distinct somewhere
but i was nt able to find the place
Thats why i posted the query as it is to get the advice from the experts like you...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

No worries
Glad that I could sort it out for you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-04-07 : 09:13:19

There is no need to use any recursive CTE but not sure of the performance though.


SELECT 	Category, 
		STUFF((SELECT ':' + Ctype FROM @TableTest WHERE Category = c.Category FOR XML PATH('')), 1,1,''),
		STUFF((SELECT distinct ',' + Location FROM @TableTest WHERE Category = c.Category FOR XML PATH('')), 1,1,'')
FROM @TableTest c
group by Category

PBUH

dpais
Yak Posting Veteran

60 Posts

Posted - 2010-04-11 : 08:25:23

thanks guys - the grouping query was a success. appreciate the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources