could you give me any example ??

Thanks 🙂

The Output shall get the Count For Each Country and Each City

I tried that ...to convert each attribute as separated Element

-----------------------------------------------------------------------

--------------------------------------------------------------------------

DECLARE @l TABLE (ID INT, [Name] VARCHAR(30), Location VARCHAR(100))

INSERT INTO @l (ID, [Name], Location)

SELECT 1, 'Ronaldo', 'Country:Spain, City:Madrid, Country:France'

UNION ALL SELECT...

thanks dwain.c

what if the cell may contain more than one country or more than one city

SELECT 1, 'Ronaldo', 'Country:Spain, City:Madrid, Country:Spain'

UNION ALL SELECT 2, 'Messi','Country:Spain, City:Barcelona, City:Madrid'

UNION ALL SELECT 3,'Rooney','Country:England,...

Thanks a lot Lynn Pettisv 🙂

OK do you have any alternative ??

Split Function Code

USE [Test]

GO

/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 04/07/2012 19:03:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))...

I got what i want by doing that

In My Example The Location Data like That

[Country] Egypt [City] Alexandria [Country] France [City] Alexandria

[Country] USA [City] New York [Country] France [City]...

Sure i know Normalization is the best solution 🙂

but that is not available in my situation ...the requirements to be like what i did ...i can't change it

no .. may be some rows will have country only without any city to be like that only (no key and no value for the city)

Country:<Country>

without any city

and may...

Let The First Example I think it was not clear enough but the last is Very Clear to my Idea ... The Last Question for You that Because as i...

I will give another Example .... if i'm Hotel Owner

and I have 3 Categories on it Normal,High,Kings Rooms

so i will have that Records

ID, StartDate, EndDate, RoomName,IsAvaliable

1 , 2-1-2011, 2-10-2011, Normal...

I Want Something Like Intersection between Records that have IsAvalibale=false... The Result of That Intersection will be Records of dates....

There is A different Between Production Date and ExpiredDate and...