How could I write this view ?
-
I have a table containing 2 string fields :
field1 field2
=============
s1 s1
s1 s2
s1 s3
s2 s1
s2 s2
s2 s3
.........
sn s1
sn s2
.........
sn sn
Is it possible to write a view that will generate this ?
f1 f2
-------------
s1 s2 & s3 & ... & sn
s2 s1 & s3 & ... & sn
.........................
sn s1 & s2 & .. & s(n-1)
& = concatenation operator
Note : n is pretty big, so joining the base table with itself 30-40 times could be pretty messy ... And is not exactly a constant - I mean s1 could have 3 rows in the base table, s2 6 rows , s3 16 rows and so on ..... Using cursors in a stored procedure is again too slow (the table is quite big ..... )
Help me please!
-
It's better to do it in function.
Search this forum for "concatenate" and choose one of functions posted here.
_____________
Code for TallyGenerator -
Use below mentioned query and it will resolve your problem.....
Select
DISTINCT Test1, STUFF( ( SELECT ',' + Test2 FROM Test A Where A.test1 = B.Test1 FOR XML PATH('')),1 ,1, '') as Test2
from
Test B
Have a nice Day
-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
-
Please post your sample data on which you want to run this query, Please find below the details which I am using:
CREATE TABLE [dbo].[Test]( [Test1] [varchar](50) COLLATE NULL, [Test2] [varchar](50) COLLATE NULL, [TESTID] [int] NOT NULL, CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [TESTID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
Sample data:
TestID Test1 Test2
-------------------
1 A1 S1
2 A2 S1
3 A3 S1
4 A1 S2
5 A2 S2
6 A3 S2
7 A1 S3
8 A2 S3
9 A3 S3
Query Posted:
Select DISTINCT Test1, STUFF( ( SELECT ',' + Test2 FROM Test A Where A.test1 = B.Test1 FOR XML PATH('')),1 ,1, '') as Test2 fromTest B
Result:
Test1 Test2
A1 S1,S2,S3
A2 S1,S2,S3
A3 S1,S2,S3
Pleasde check your data and let me know if there is any difference.....
-
For XML Path is avaible first with SQL Server 2005.
N 56°04'39.16"
E 12°55'05.25" -
Ooops , I'm using SQL Server 2000 :-((
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply