Need help with query

Question

Post reply

Need help with query

anhlongqld

Grasshopper

Points: 12
More actions
June 22, 2013 at 7:09 am

#275467

Please help, I've been searching for days but could not find any useful info. to resolve the below query.

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

maybe something along these lines will help you get started

SELECT

CALLNO,

TITLE,

STUFF ( (

SELECT ',' + ARTIST

FROM YOURTABLE p2

WHERE p1.CALLNO = p2.CALLNO

ORDER BY p2.ARTIST

FOR XML PATH ( '' )) , 1 , 1 , ' ' ) AS ARTIST,

STUFF ( (

SELECT ',' + GENRE

FROM YOURTABLE p2

WHERE p1.CALLNO = p2.CALLNO

ORDER BY p2.GENRE

FOR XML PATH ( '' )) , 1 , 1 , ' ' ) AS GENRE,

LYRIC

FROM YOURTABLE p1

GROUP BY CALLNO, TITLE, LYRIC;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

anhlongqld Grasshopper Points: 12 More actions · Answer 2

I am using MSAccess doesn't seem to have STUFF and XML Path function. Thankyou and I appreciated your time trying to help me out.

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

I don't think there is anyway to do this kind of thing directly in Access. The query engine is just not that robust. You will probably have to do this in a loop in VBA to pull it off.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

maybe some ideas here

http://social.msdn.microsoft.com/Forums/office/en-US/69faba1b-00ef-446b-8727-a59da828ac87/equivalent-function-for-ms-sql-stuff-function

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day