Comma Seperated logic over a 65000 Records.

Question

2.55/5 (4 votes)

See more:

Hey guys,

Please help me in building a logic that gives a comma seperated result set based on 3 columns and over a 65000 record set.
I've tried using STUFF, COALESCE, SUBSTRING, by the time the query completes execution, I can make a cup of tea.. :( :P.

Note:
SQL Server : MSSQL 2005.

HTML

type	Country	        Year	filename	FileID
A	Singapore	2010	A.xls	        20
A	Singapore	2010	B.xls	        21
A	Singapore	2010	C.Xls	        22
A	Malaysia	2008	A.xls	        55
A	Malaysia	2008	B.xls	        53
B	Malaysia	2008	Cat.xls	        88
B	Malaysia	2008	doll.xls	78
				
				
Expected output				
type	Country	        Year	filename	        FileID
A	Singapore	2010	a.xls,b.xls,c.xls	20,21,22
A	Malaysia	2008	a.xls,b.xls	        55,53
B	Malaysia	2008	cat.xls,doll.xls	88,78

Posted 10-May-14 4:11am

Venkat Devireddy

Updated 10-May-14 4:57am

v4

Add a Solution

Comments

DamithSL 10-May-14 10:14am

update the question with your code

Venkat Devireddy 10-May-14 10:30am

Hi Damith, due to privacy, I cant share my code here., please send a test mail () I will share my code. Well, i've used the similar logic the one here(http://goo.gl/EJ1jgP)..

CHill60 11-May-14 9:06am

You might want to remove that email address before a spambot finds it

Venkat Devireddy 11-May-14 9:27am

thank you CHill

[no name] 10-May-14 10:49am

You would need to tell us what sort of "help" you need. We cannot see your code, your screen or your server to know what the problem might possibly be.

Venkat Devireddy 10-May-14 10:58am

Hi Wes Aday,
1) I've exactly used the same logic which is here : http://goo.gl/EJ1jgP
2) i've updated my question with sample input data with expected result. Hope that helps you.

King Fisher 10-May-14 15:43pm

what have you tried? show your query

Venkat Devireddy 10-May-14 15:49pm

Hi King_Fisher,

below is the query..

SELECT
[type],
Country,
[Year],
fileName =
STUFF((SELECT ', ' + fileName
FROM Records FileNameRecords
WHERE
FileNameRecords.[type] = Records.[type]
AND FileNameRecords.Country = Records.Country
AND FileNameRecords.[Year] = Records.[Year]
FOR XML PATH('')), 1, 2, ''),
FileId =
STUFF((SELECT ', ' + FileId
FROM Records FileNameRecords
WHERE
FileNameRecords.[type] = Records.[type]
AND FileNameRecords.Country = Records.Country
AND FileNameRecords.[Year] = Records.[Year]
FOR XML PATH('')), 1, 2, '')
FROM
Records
GROUP BY
[type], Country, [Year]
ORDER BY
type, Year DESC

Nelek 11-May-14 18:39pm

You should use the "improve question" widget to add information or code snippets to the original message. So you will be able to add code tags and format it correctly, making it easier to read for the people who can help.

Jörgen Andersson 10-May-14 18:01pm

How does your indexes look like?

Venkat Devireddy 10-May-14 23:10pm

well, all the data i'm saving to a table variable which is hardly taking a seconds, post which the above comma separated logic used which is taking too much time. So, INDEXES on this table variable shouldn't cause the delay, right.?

CHill60 11-May-14 9:07am

Nope - but they might speed up the read from table [Records] !

Jörgen Andersson 11-May-14 14:24pm

As CHill said, it won't cause any delays when querying. When inserting and updating it will, but that's an entirely other question.
The problem is that by using a Table Variable you have effectively prevented the DB from using an index when aggregating the query.

Yes I know it's possible to create indexes on Table Variables, but only unique ones.

So the follow up question is whether you're actually having just one source table or if it's several joined tables.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Answer 1 · 2014-05-10T05:31:00

Any effort?

Anyway, this should work:

SELECT 
	[type], 
	Country, 
	[Year], 
	fileName = 
		STUFF((SELECT ', ' + fileName
           FROM Records FileNameRecords
           WHERE 
				FileNameRecords.[type] = Records.[type]
				AND FileNameRecords.Country = Records.Country
				AND FileNameRecords.[Year] = Records.[Year]
          FOR XML PATH('')), 1, 2, ''),
    FileId = 
		STUFF((SELECT ', ' + FileId
           FROM Records FileNameRecords
           WHERE 
				FileNameRecords.[type] = Records.[type]
				AND FileNameRecords.Country = Records.Country
				AND FileNameRecords.[Year] = Records.[Year]
          FOR XML PATH('')), 1, 2, '')
FROM	
	Records
GROUP BY
	[type], Country, [Year]
ORDER BY
	type, Year DESC

Maciej Los · Answer 2 · 2014-05-11T02:57:00

If you want to use CTE, please see:

SQL

DECLARE @tmp TABLE (atype VARCHAR(5), Country VARCHAR(30), aYear INT, afilename VARCHAR(30), FileID INT)

INSERT INTO @tmp (atype, Country, aYear, afilename, FileID)
VALUES('A', 'Singapore', 2010, 'A.xls', 20),
('A', 'Singapore', 2010, 'B.xls', 21),
('A', 'Singapore', 2010, 'C.Xls', 22),
('A', 'Malaysia', 2008, 'A.xls', 55),
('A', 'Malaysia', 2008, 'B.xls', 53),
('B', 'Malaysia', 2008, 'Cat.xls', 88),
('B', 'Malaysia', 2008, 'doll.xls', 78)

;WITH CTE AS
(
    SELECT atype, Country, aYear, afilename, CONVERT(VARCHAR(MAX),afilename) AS fnames, FileID, CONVERT(VARCHAR(MAX),FileID) AS fIds, 1 AS LoopNo
    FROM @tmp
    UNION ALL
    SELECT C.atype, C.Country, C.aYear, C.afilename, C.fnames + ',' + T.afilename AS fnames, C.FileID, C.fids + ',' + T.FileId AS fIds, LoopNo + 1 AS LoopNo
    FROM CTE AS C INNER JOIN (
        SELECT atype, Country, aYear, CONVERT(VARCHAR(MAX),afilename) AS afilename, CONVERT(VARCHAR(MAX),FileID) AS FileID
        FROM @tmp
        ) AS T ON C.aType = T.aType AND C.Country = T.Country AND C.aYear = T.aYear AND C.FileID < T.FileID
    WHERE CHARINDEX(CONVERT(NVARCHAR(10),T.FileID), C.fIds)=0
)
SELECT C.atype, C.Country, C.aYear, fnames, fIds
FROM CTE AS C
ORDER BY C.atype, C.Country, C.aYear

Above query produces:

A	Malaysia	2008	A.xls			55
A	Malaysia	2008	B.xls			53
A	Malaysia	2008	B.xls,A.xls		53,55
A	Singapore	2010	B.xls,C.Xls		21,22
A	Singapore	2010	A.xls,B.xls		20,21
A	Singapore	2010	A.xls,C.Xls		20,22
A	Singapore	2010	A.xls,C.Xls,B.xls	20,22,21
A	Singapore	2010	A.xls,B.xls,C.Xls	20,21,22
A	Singapore	2010	A.xls			20
A	Singapore	2010	B.xls			21
A	Singapore	2010	C.Xls			22
B	Malaysia	2008	Cat.xls			88
B	Malaysia	2008	doll.xls		78
B	Malaysia	2008	doll.xls,Cat.xls	78,88

There is no guarantee that CTE will be faster than in-build SQL functions. Change the code to your needs.

Note: Above sample code shows how CTE work.

For further information about CTE, please see:
WITH common_table_expression (Transact-SQL)[^]
CTEs (Common Table Expressions)[^]
CTE In SQL Server[^]

Jörgen Andersson · Answer 3 · 2014-05-11T10:06:00

Maciejs solution reminded me that I solved that problem a couple of years ago. Link[^]
Adjusted for your needs it would look similar to this:

SQL

CREATE TABLE Records
	([type] varchar(1), [Country] varchar(9), [Year] int, [filename] varchar(8), [FileID] int)
;

CREATE INDEX records_ix
     ON Records
       ([type], [Country], [Year], [filename])
     INCLUDE ([FileID]);
	
INSERT INTO Records
	([type], [Country], [Year], [filename], [FileID])
VALUES
	('A', 'Singapore', 2010, 'A.xls', 20),
	('A', 'Singapore', 2010, 'B.xls', 21),
	('A', 'Singapore', 2010, 'C.Xls', 22),
	('A', 'Malaysia', 2008, 'A.xls', 55),
	('A', 'Malaysia', 2008, 'B.xls', 53),
	('B', 'Malaysia', 2008, 'Cat.xls', 88),
	('B', 'Malaysia', 2008, 'doll.xls', 78)
;

WITH ranked AS (
    SELECT  [type], [Country], [Year], [filename], [FileID], Rank() over (PARTITION BY [type], [Country], [Year] ORDER BY [filename]) Rnk 
    FROM    Records
    )
,cte ([type], [Country], [Year], FileNames, FileIDs, rnk) as
    (
    SELECT  [type]
           ,[Country]
           ,[Year]
           ,cast([FileName] as varchar(max)) as Filenames
           ,cast([FileID] as varchar(max)) as FileIDs
           ,rnk
    FROM    Ranked r
    WHERE   rnk = 1
    UNION ALL
    SELECT  cte.[type]
           ,cte.[Country]
           ,cte.[Year]
           ,FileNames + ', ' + [FileName]
           ,FileIDs + ', ' + cast([FileID] as varchar(max))
           ,r.rnk
    FROM    cte
    INNER JOIN ranked r
        ON  cte.[type] = r.[type]
        AND cte.[Country] = r.[Country]
        AND cte.[Year] = r.[Year]
        AND cte.Rnk = r.rnk - 1
    )
SELECT  [type], [Country], [Year], Max(FileNames) FileNames, Max(FileIDs) FileIDs, Max(rnk)
FROM    cte
GROUP BY [type], [Country], [Year]
ORDER BY [type], [Year] desc

Note the covering composite Index on the record table.
Still no guarantee that it's faster, but a proper index is important.

Jörgen Andersson · Answer 4 · 2014-05-11T21:28:00

Just realized there is yet another way to do this.
You can create your own aggregate.
In VS create a new SQL Server Database Project, add a new item "SQL CLR C# Aggregate".
Add code similar to this:

C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct ListAgg
{
    private List<sqlstring> values;

    public void Init()
    {
        this.values = new List<sqlstring>();
    }

    public void Accumulate(SqlString Value)
    {
        this.values.Add(Value.Value);
    }

    public void Merge (ListAgg Group)
    {
        this.values.AddRange(Group.values.ToArray());
    }

    public SqlString Terminate ()
    {
        return new SqlString(string.Join(", ", this.values.ToArray()));
    }
}

Build and deploy[^] this assembly on the server.

Now you should be able to run a query like this:

SQL

SELECT  [type]
       ,[Country]
       ,[Year]
       ,dbo.ListAgg([filename])
       ,dbo.ListAgg([FileID])
FROM    Records
GROUP BY [type]
        ,[Country]
        ,[Year]

This solution should be running very fast compared to the others, BUT, note that this is untested and should be thought of as an idea of how you can solve your problem.

Comma Seperated logic over a 65000 Records.

4 solutions

Solution 1

Solution 4

Solution 5

Solution 6

Add your solution here

Preview 0