T-SQL Prob - 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

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL Prob

Author

Topic

oraclevoid
Starting Member

3 Posts

Posted - 2010-05-31 : 03:28:11

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-31 : 03:31:30

Is there a chance to do it in your front end?

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

oraclevoid
Starting Member

3 Posts

Posted - 2010-05-31 : 03:39:04

I could but I would really like to know if theres a way I could to it in sql...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-31 : 03:57:53

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

oraclevoid
Starting Member

3 Posts

Posted - 2010-05-31 : 04:57:21

@madhivanan: sorry but i really didnt get what that they were talking about in that url... >.<

please help! I really need this done in sql if possible.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-31 : 05:11:51

quote:
Originally posted by oraclevoid

@madhivanan: sorry but i really didnt get what that they were talking about in that url... >.<

please help! I really need this done in sql if possible.

Execute the code posted in the link and understand

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-31 : 06:31:27

Hi oraclevoid.

Madhivanan's posted a link which shows how to abuse the FOR XML PATH() functionality to concatenate rows into a string. It's pretty in-depth but is probably the fastest way to do this in (MS SQL SERVER) SQL.

If nothing else, the link should show you WHY this is better done in the front end. writing a nicely performant loop in whatever application language you are using would be pretty simple over the result set you all-ready have.

Here's a really toned down example though using FOR XML clause.


-- Make an example table
DECLARE @dataSet TABLE (
	[Fruit] VARCHAR(255)
	, [brand] CHAR(1)
	, [location] INT
	)

-- Populate the data
INSERT @dataSet ([Fruit], [brand], [location])
      SELECT 'apple', 'X', 1
UNION SELECT 'apple', 'Y', 1
UNION SELECT 'banana', 'Z', 2
UNION SELECT 'banana', 'O', 2

-- Select the data in the required format
SELECT
	products.[fruit]
	, LEFT(brandList.[brands], LEN(brandlist.[brands]) - 1 ) AS [Brand List]
	, products.[location]
FROM
	(
	SELECT DISTINCT
		[Fruit] AS [Fruit]
		, [location] AS [Location]
	FROM
		@dataSet
	)
	products

	CROSS APPLY (
		SELECT
			f.[brand] + ', '
		FROM
			@dataSet f
		WHERE
			f.[Fruit] = products.[fruit]
			AND f.[location] = products.[location]
		ORDER BY
			f.[brand]
		FOR XML PATH('')
		)
		brandList ([brands])

Now this may contain a few constructs that you are not familiar with. I'll explain simply:

The first derived table (which I've called products) just brings back a distinct list of fruits and locations for us to base the concatenation on.

The CROSS APPLY clause you may not have seen before. CROSS APPLY essentially performs an operation on EACH ROW of the parent result set (but does it very quickly). (in this case the derived table products). This part is essentially building a String of brands for each fruit and location pair. Finally when we name the cross apply segment (I've called it brandList) we have to provide a name for the column that it returns.(which is brands).

Because [brands] has an extra comma at then end of it we have to remove that in the outer SELECT query.

This might be a lot to digest. It's safe to just cut and paste this into a management stuio code window and run anywhere (as long as the database is compatability level 90 or above -- so SQL SERVER 2005 or later).

Play around and ask questions.

However -- as you can see this is a complicated way to do something that would be very simple in an application.

Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Julien.Crawford
Starting Member

21 Posts

Posted - 2010-05-31 : 19:51:26

I seem to be providing similar sql each time I post :-)

This uses a single pass update, so the order of the rows IS IMPORTANT.
If you have a clustered index then great.
Also, I've needed to add a new column for your data.
(And another also, You are going to need to drop the rows you don't want).

(Sorry to be so incomplete)

-- Make an example table
DECLARE @dataSet TABLE (
[Fruit] VARCHAR(255)
, [brand] CHAR(1)
, [xx] varchar(2000)
, [location] INT
)

-- Populate the data
INSERT @dataSet ([Fruit], [brand], [location])
SELECT 'apple', 'X', 1
UNION SELECT 'apple', 'Y', 1
UNION SELECT 'apple', 'Z', 1
UNION SELECT 'banana', 'Z', 2
UNION SELECT 'banana', 'O', 2

-- This is the magic bit.
declare @prevFruit varchar(2000)
set @prevFruit = ''
declare @s varchar(2000)
set @s = ''

update @dataSet
set @s = case when [Fruit] <> @prevFruit then brand else @s + ',' + brand end
, [xx] = case when [Fruit] <> @prevFruit then [xx] else @s end
, @prevFruit = [Fruit]

select * from @dataSet

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-01 : 04:19:00

quote:
Originally posted by Julien.Crawford

I seem to be providing similar sql each time I post :-)

13 posts and most of them have been quirky updates? That's pretty hardcore!

oracleVoid : Julien.Crawford's code is generally used to quickly perform operations where you need the previous row's data (defined in some way) to work with. A running total is a good example if you have a list of dates and purchases / sales and need to calculate how much stock you'd have at any one time in the past. It's an extremely fast method for that but it is critically dependant on the clustered index of the table.

To be honest, all the methods posted here should tell you that doing this in SQL isn't a great plan. there's nothing wrong with the result set you have already. pivoting it in the way that you want is a trivial task in your presentation layer.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources