Help on sql query - Aaron Bertrand [SQL Server MVP]

You forgot to tell us which version of SQL Server you are using.  If 2005,
you can check out this article (e.g. the FOR XML PATH methods):

http://www.aspfaq.com/2529



On 5/7/08 12:35 PM, in article
97b8cc87-cc30-4641-a38d-240392059fae@c58g2000hsc.googlegroups.com, "mirth"

Help on sql query - Aaron Bertrand [SQL Server MVP]

I didn't have the second post in my local cache when I responded.  In any
case, the article still might be useful, as I have no idea how well you have
optimized the function you wrote.

Or, do yourself a favor, and perform this concatenation at the client /
presentation tier.  Since it has to loop through every row anyway, this will
likely be the faster place to do it.


On 5/7/08 2:18 PM, in article
19957ea0-fa7c-49ee-9ac8-a646f0f6c4c5@34g2000hsh.googlegroups.com, "mirth"

Help on sql query - Michael Ware

Mirth,

Try this in Query Analyzer as an example:

GO
CREATE TABLE dbo.tmpFlowers
(
Nm NVARCHAR(50),
Clr NVARCHAR(10),
);

INSERT dbo.tmpFlowers
SELECT 'Rose', 'Red'
UNION ALL SELECT 'Rose', 'Yellow'
UNION ALL SELECT 'Rose', 'Pink'
UNION ALL SELECT 'Dassy', 'Yellow'
UNION ALL SELECT 'Dassy', 'White'
UNION ALL SELECT 'Iris', 'Yellow'
UNION ALL SELECT 'Iris', 'Blue'
UNION ALL SELECT 'Mum', 'Purple'
UNION ALL SELECT 'Mum', 'Red'
UNION ALL SELECT 'Mum', 'Peach'
UNION ALL SELECT 'Mum', 'Yellow'
UNION ALL SELECT 'Mum', 'White'
GO
DROP TABLE #tempFClr
GO
CREATE TABLE dbo.#tempFClr
(
Nm NVARCHAR(50),
Clrs NVARCHAR(100),
LastClr NVARCHAR(10),
);
INSERT dbo.#tempFClr
SELECT NM, Min(Clr), Min(Clr)
FROM dbo.tmpFlowers
GROUP BY NM
WHILE @@ROWCOUNT > 0
BEGIN
UPDATE dbo.#tempFClr
SET Clrs = Clrs + ',' + N.NextClr , LastClr = N.NextClr
FROM  dbo.#tempFClr INNER JOIN
(SELECT F.NM, MIN(F.Clr) AS NextClr
FROM dbo.tmpFlowers F INNER JOIN
dbo.#tempFClr T ON F.NM = T.NM AND F.Clr>T.LastClr
GROUP BY F.NM ) N ON N.NM = #tempFClr.NM
END

go
SELECT * FROM dbo.#tempFClr
go
DROP TABLE tmpFlowers
DROP TABLE #tempFClr
go


-Mike

----------------------
Lower your voice and strengthen your argument. -Lebanese proverb

Help on sql query - mirth

Hi guys,

I have a table flowercolor having two columns FlowerID and ColorID.
One flowerID can have multiple ColorID like the below examples:

FlowerID	ColorID
1	2
1	3
2	1
3	1
4	2
4	4
4	5

I have to concatenate the ColorID as string for the same FlowerID and
transfer the table like:

FlowerID	ColorID
1	2,3
2	1
3	1
4	2,4,5

I wrote a function to use cursor to concatenate the string, but it's
so slow. (The above is just a made-up example, not real case). Does
anyone here know any better way to solve this problem not using the
cursor? I don't know if there's any way to use derived table or
subselect or anything faster. Any help is appreciated!!!

Thanks,
mirth

Help on sql query - mirth

btw: I'm using sql server 2000

Help on sql query - mirth

IT's sql server 2000. I said in the second post. :)

On May 7, 11:54=A0am, "Aaron Bertrand [SQL Server MVP]"
5,

Help on sql query - --CELKO--

Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure?  Normal forms are the foundation of RDBMS, after
all.

Why are you formatting data in the back end?  The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end.  This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.

Help on sql query - mirth

Thank you so much Michael!

stClr