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 2008 Forums
 Transact-SQL (2008)
 PATINDEX
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JanakiRam
Starting Member

India
22 Posts

Posted - 06/22/2013 :  03:14:18  Show Profile  Reply with Quote
Hi.,

Need a query for below mentioned result

DECLARE @Variable VARCHAR(MAX)
SELECT @Variable = '''8888;9999;5555;'',''2222;1111;'
SELECT @Variable


DECLARE @Table TABLE (EmployeeId INT, Name VARCHAR(256))
INSERT INTO @Table
SELECT 8888,'J'
INSERT INTO @Table
SELECT 9999,'A'
INSERT INTO @Table
SELECT 5555,'N'
INSERT INTO @Table
SELECT 2222,'A'
INSERT INTO @Table
SELECT 1111,'K'
INSERT INTO @Table
SELECT 9999,'I'

OUT PUT:

8888,9999
9999,5555
8888,5555
2222,1111
9999,1111

Means N*N Time table have 9 rows then probability have 9*9 = 81 cases needed

JanakiRam

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/22/2013 :  10:51:38  Show Profile  Reply with Quote
Is the data already in the table, or is it in @Variable as semi-colon separated values? Assuming it is in the table, if you want to get all combintions,
SELECT a.EmployeeId, b.EmployeeId FROM @Table a CROSS JOIN @Table b;
Go to Top of Page

JanakiRam
Starting Member

India
22 Posts

Posted - 06/24/2013 :  03:23:42  Show Profile  Reply with Quote
DECLARE @Variable VARCHAR(8000)

SELECT @Variable = '1111,8888,9999,2222,5555'


DECLARE @List TABLE (EmployeeId VARCHAR(8000))
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(',',@Variable,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@Variable,1,CHARINDEX(',',@Variable,0)-1))),
@Variable=RTRIM(LTRIM(SUBSTRING(@Variable,CHARINDEX(',',@Variable,0)+LEN(','),LEN(@Variable))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

JanakiRam
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 06/24/2013 :  03:33:21  Show Profile  Reply with Quote

DECLARE @Variable VARCHAR(MAX)
SELECT @Variable = '1111,8888,9999,2222,5555'

select	v1.stringval, v2.stringval
from	CSVTable(@Variable) v1
	cross join CSVTable(@Variable) v2

get CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable


KH
Time is always against us

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.05 seconds. Powered By: Snitz Forums 2000