T-sql help for grouping - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

T-sql help for grouping

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-06-08 : 11:06:01

Hey Guys,
I am sure there is easier way to do this other then what I am doing...

what I need is set of files that has same fields... I have a table which has field names and file names... need to find out what files can be grouped together as they have same fields...

fieldname filename
============================
id file1
add1 file1
id file2
add1 file2
state file2
id file3
add1 file3

So what I need is file1 and file3 to be grouped together so that I can create one table for those two files... Thanks in advance for help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 11:08:59

About your example it looks like you mean file1 and file3?

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 11:09:22

How do you want them grouped? Can you show us your expected output.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-06-08 : 11:18:58

quote:
Originally posted by webfred

About your example it looks like you mean file1 and file3?

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes file1 and file3 ... sorry for the mistake changed it..

The output can be a ranking probably...

file1 1
file2 2
file3 1
.....

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-08 : 11:49:13

Check this

CREATE TABLE #TABLE1 (fieldname VARCHAR(20), filename1 VARCHAR(20))
CREATE TABLE #TABLE2 (filename1 VARCHAR(20), ColumnList VARCHAR(4000))
INSERT INTO #TABLE1
SELECT 'id','file1' UNION ALL
SELECT 'add1','file1' UNION ALL
SELECT 'id','file2' UNION ALL
SELECT 'add1','file2' UNION ALL
SELECT 'state','file2' UNION ALL
SELECT 'id','file3' UNION ALL
SELECT 'add1','file3'

INSERT INTO #TABLE2
SELECT Distinct Names.filename1, SUBSTRING(CONVERT(VARCHAR(50),iTVF.Decode ),2,LEN(CONVERT(VARCHAR(50),iTVF.Decode ))) AS ColumnsList
FROM #TABLE1 Names
CROSS APPLY (
SELECT ',' + CONVERT(VARCHAR(20), ISNULL(t1.fieldName, '*'))
FROM #Table1 T1
WHERE Names.filename1 = T1.filename1
FOR XML PATH (''), TYPE
) iTVF ([Decode])

SELECT T1.FileName1,T2.FileName1
FROM #Table2 T1
INNER JOIN #Table2 T2
ON T1.FileName1<>T2.FileName1
AND T1.ColumnList= T2.ColumnList
AND T1.FileName1<T2.FileName1
DROP TABLE #TABLE1
DROP TABLE #TABLE2

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-06-08 : 12:23:09

Thanks a lot Gopi.. this is something I was working on putting all fields in a string, but without using xml path... this is good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources