Concatenate Data with a twist

Question

Post reply

Concatenate Data with a twist

Digs

SSCertifiable

Points: 7333
More actions
November 21, 2010 at 1:46 pm

#243076

Data first..

IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test; CREATE TABLE #test ( [Date] DATETIME, Symbol VARCHAR(10), UpDown INT, ) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091026','AAPL',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091026','BB',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091026','MSFT',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091026','BNC',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091026','GOOG',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091026','GE',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091027','AAPL',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091027','BB',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091027','MSFT',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091027','BNC',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091027','GOOG',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091027','GE',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091028','AAPL',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091028','BB',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091028','MSFT',0) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091028','BNC',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091028','GOOG',1) INSERT INTO #test ([Date],Symbol,UpDown) VALUES ('20091028','GE',1) CREATE CLUSTERED INDEX ix_t on #test([Date]) DECLARE @List VARCHAR(8000) SELECT @List = COALESCE(@List +'|','')+ [Symbol] FROM #test WHERE UpDown = 1 SELECT @List IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;

The above output is this :

AAPL|BB|BNC|GE|AAPL|GE|BNC|GOOG|GE

What I am after is this:

20091026, AAPL|BB|BNC|GE

20091027, AAPL|GE

20091028, BNC|GOOG|GE

How is this done ?:-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Sounds like FOR XML PATH is the way to go:

SELECT



	DATE,

	STUFF((SELECT '|' + Symbol FROM #test t2 WHERE t2.date = t1.date AND UpDown = 1 ORDER BY symbol FOR XML PATH('')),1,1,'')

FROM    #test t1

GROUP BY DATE

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Digs SSCertifiable Points: 7333 More actions · Answer 2

Thanks, I added one more complication, another grouping field called 'Type', I get code errors..

Any ideas..



IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;

CREATE TABLE #test

(

[Date] DATETIME,

[Type] VARCHAR(10),

Symbol VARCHAR(10),

UpDown INT,

)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091026','MA50','AAPL',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091026','MA50','BB',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091026','MA50','MSFT',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091026','MA50','BNC',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091026','MA50','GOOG',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091026','MA50','GE',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091027','MA50','AAPL',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091027','MA50','BB',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091027','MA50','MSFT',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091027','MA50','BNC',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091027','MA50','GOOG',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091027','MA50','GE',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091028','MA50','AAPL',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091028','MA50','BB',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091028','MA50','MSFT',0)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091028','MA50','BNC',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091028','MA50','GOOG',1)

INSERT INTO #test ([Date],[Type],Symbol,UpDown) VALUES ('20091028','MA50','GE',1)

CREATE CLUSTERED INDEX ix_t on #test([Date])

SELECT

        DATE,[TYPE]

        STUFF((SELECT '|' + Symbol FROM #test t2 WHERE t2.date = t1.date AND UpDown = 1 ORDER BY symbol FOR XML PATH('')),1,1,'')

FROM    #test t1

GROUP BY DATE,[TYPE]

IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;

I am after:

20091026, MA50, AAPL|BB|BNC|GE

20091027, MA50, AAPL|GE

20091028, MA50, BNC|GOOG|GE

LutzM SSC Guru Points: 107049 More actions · Answer 3

LutzM

SSC Guru

Points: 107049

November 21, 2010 at 3:13 pm

#1253528

How about adding a comma after the [TYPE] column?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Digs SSCertifiable Points: 7333 More actions · Answer 4

Digs

SSCertifiable

Points: 7333

November 21, 2010 at 3:22 pm

#1253529

Ha ha...Done !

Thanks...for the help !:-):-D:-)