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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Recursive CTE's - Please help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GarDebs2010
Starting Member

4 Posts

Posted - 09/30/2010 :  10:32:13  Show Profile  Reply with Quote
Hi,

I've been racking my brain on this one for a little while and thought I reach out and ask for some help...

Using the script below... how can I get my data to look like the expected output shown here... I've been playing with recursive CTE's but not making much progress. Any help would be greatly appreciated.

Cheers
Gary


Expected Output
Region........................Oil-producing countries
----------------------- --------------------------------------
Central America............Mexico,Guatemala,Nicaragua,Belize
Eastern Africa..............Zambia,Madagascar

Script
CREATE DATABASE [TestDb]
GO
USE [TestDb]
GO
/****** Object: Table [dbo].[OilProducers] Script Date: 09/30/2010 15:23:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OilProducers](
[OilProducer_ID] [int] IDENTITY(1,1) NOT NULL,
[Country] [varchar](30) NOT NULL,
[BarrelsPerDay] [int] NOT NULL,
[Continent] [varchar](80) NOT NULL,
[Region] [varchar](80) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[OilProducers] ON
INSERT [dbo].[OilProducers] ([OilProducer_ID], [Country], [BarrelsPerDay], [Continent], [Region]) VALUES (28, N'Mexico', 3824000, N'Latin America and the Caribbean', N'Central America')
INSERT [dbo].[OilProducers] ([OilProducer_ID], [Country], [BarrelsPerDay], [Continent], [Region]) VALUES (75, N'Guatemala', 16370, N'Latin America and the Caribbean', N'Central America')
INSERT [dbo].[OilProducers] ([OilProducer_ID], [Country], [BarrelsPerDay], [Continent], [Region]) VALUES (79, N'Nicaragua', 14300, N'Latin America and the Caribbean', N'Central America')
INSERT [dbo].[OilProducers] ([OilProducer_ID], [Country], [BarrelsPerDay], [Continent], [Region]) VALUES (95, N'Belize', 2413, N'Latin America and the Caribbean', N'Central America')
INSERT [dbo].[OilProducers] ([OilProducer_ID], [Country], [BarrelsPerDay], [Continent], [Region]) VALUES (106, N'Zambia', 140, N'Africa', N'Eastern Africa')
INSERT [dbo].[OilProducers] ([OilProducer_ID], [Country], [BarrelsPerDay], [Continent], [Region]) VALUES (109, N'Madagascar', 91, N'Africa', N'Eastern Africa')
SET IDENTITY_INSERT [dbo].[OilProducers] OFF

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/30/2010 :  10:36:06  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Why is this table un-normalised?

(why is there not a table of Regions? with a link from this table to a regions table.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

GarDebs2010
Starting Member

4 Posts

Posted - 09/30/2010 :  10:41:52  Show Profile  Reply with Quote
This is just a sample table and not a working example hence why is isn't normalised.. just looking for help for help in relation to this specific problem.

Cheers

quote:
Originally posted by Transact Charlie

Why is this table un-normalised?

(why is there not a table of Regions? with a link from this table to a regions table.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/30/2010 :  10:42:19  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Here's one way

SELECT
	region.[RegionName] AS [Region]
	, LEFT(countries.[Countries], LEN(countries.[Countries]) -1) AS [Oil-producing countries]
FROM
	(
	SELECT DISTINCT [Region] AS [RegionName] FROM OilProducers
	)
	AS region

	CROSS APPLY (
		SELECT op.[Country] + ','
		FROM OilProducers AS op
		WHERE op.[Region] = region.[RegionName]
		ORDER BY op.[Country]
		FOR XML PATH ('')
		)
		AS countries ([Countries])

It would be easier (and more efficient) with a normalised approach and a region table with a foreign key from oilProducers. (you wouldn't need the SELECT DISTINCT... derived table)

Results:

Region	Oil-producing countries
Central America	Belize,Guatemala,Mexico,Nicaragua
Eastern Africa	Madagascar,Zambia


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

GarDebs2010
Starting Member

4 Posts

Posted - 09/30/2010 :  10:45:07  Show Profile  Reply with Quote
Fairplay, ten-out-of-ten! You certainly live up to your name... that works a treat!


quote:
Originally posted by Transact Charlie

Here's one way

SELECT
	region.[RegionName] AS [Region]
	, LEFT(countries.[Countries], LEN(countries.[Countries]) -1) AS [Oil-producing countries]
FROM
	(
	SELECT DISTINCT [Region] AS [RegionName] FROM OilProducers
	)
	AS region

	CROSS APPLY (
		SELECT op.[Country] + ','
		FROM OilProducers AS op
		WHERE op.[Region] = region.[RegionName]
		ORDER BY op.[Country]
		FOR XML PATH ('')
		)
		AS countries ([Countries])

It would be easier (and more efficient) with a normalised approach and a region table with a foreign key from oilProducers. (you wouldn't need the SELECT DISTINCT... derived table)

Results:

Region	Oil-producing countries
Central America	Belize,Guatemala,Mexico,Nicaragua
Eastern Africa	Madagascar,Zambia


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/30/2010 :  10:48:50  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
the FOR XML PATH('') method for row concatenation is a bit of a hack.

Presentation like this should be handled in your front end. It's easier (and more performant) to simply return an ordered list from the database and then iterate of the rows building up output in whatever application language you are using..

My comments about the normalisation still all apply but I get the impression you know what you are doing.

good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
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.06 seconds. Powered By: Snitz Forums 2000