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

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:-)