April 23, 2010 at 9:31 pm
hi all,
I am presently working on a solution to the following problem:
Table 1: index1 (primary key),filename
Table 2: index2 (primary key), index1(Table1),param.
Output :
Filename..........param
name1.pdf..........2345,5678,908
name2.pdf..........456,89,103
and so on...
For each filename there are multiple params.
Using a single table i could implement the functionality in MySQl with the group_contact() function.Is their any similar function available in MSSQl???
I don't mind going back to using a single table again here too!
Thanks!
April 23, 2010 at 11:46 pm
Harsha, there are lot of ways (fast ways included) for performing group concatenation in MS SQL... if u could please post us DDLs for table structures (CREATE TABLE scripts), sample records ( INSERT INTO <table> SELECT scripts), constraint/indexes (again create scripts) and DESIRED output in any form , then the good fellas here will provide you back with **tested and optimized** code 🙂
Cheers!!
April 24, 2010 at 4:33 am
A good summary of the available methods here:
http://florianreischl.blogspot.com/2010/01/concatenation-of-text-and-binary-data.html
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 29, 2010 at 8:09 am
Hi All,
My Table Structure :
CREATE TABLE [dbo].[auto] ( [id] [int] IDENTITY (100, 2) NOT NULL ,
[HS1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filename] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
insert into auto (HS1,filename) values('123', '12-3-09.pdf');
insert into auto (HS1,filename) values('12345', '12-3-09.pdf');
insert into auto (HS1,filename) values('12367', '12-3-09.pdf');
insert into auto (HS1,filename) values('312', '12-4-09.pdf');
insert into auto (HS1,filename) values('4567', '12-5-09.pdf');
Required Output :
Filename
12-3-09.pdf
12-4-09.pdf
For each filename their could be multiple HS1's.
I don't want the HS1's to be combined as a single string.
for example when i use a select query to display this particular filename 12-3-09.pdf it should show 3 HS1 are linked to it not as a single HS1 string separated by commas or spaces.
The table structure can be changed and we could use 2 tables also.But i want the output as shown above.
Any Help would b appreciated..
Thanks!!!!
April 30, 2010 at 9:41 am
Your output does not match your supplied data!
You have a row for filename '12-5-09.pdf' in your table which doesn't appear in your output, and two rows for filename '12-4-09.pdf' in your output but only one in the table.
Do you really want blank values in your output for repeated values of filename?
Either way try this:
SELECT
CASE WHEN ROW_NUMBER() OVER(PARTITION BY filename order by HS1) = 1 THEN
filename
ELSE
''
END,
HS1
FROM
auto
ORDER BY
filename,HS1
April 30, 2010 at 9:44 am
... are you trying to do formatting in SQL that would be better done in your application or report?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply