Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

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