Function - Spilt Columns into multiple rows

Question

Post reply

Function - Spilt Columns into multiple rows

graham 47698

SSC Journeyman

Points: 99
More actions
October 19, 2012 at 6:20 pm

#268776

Hi
I have a table which has columns containing sales values for each month for example..
Id, versionId, jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun
The 'versionId' joins to the period table which has the end dates for each month
versionId, juldate,augdate,sepdate,octdate,novdate,decdate,jandate,febdate,madater,aprdate,maydate,jundate
What i need to do is return individual rows for each month with the corresponding period date..
Id
versionId
salesvalue
salesenddate
I have been looking at Multi-Statement Table-Value Functions but haven't been able to get anywhere near this result..
Any assistance greatly appreciated..

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

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

justmohit SSC Veteran Points: 280 More actions · Answer 1

Please provide table structures and the the output you want clearly. Then only someone can suggest something.

dave-dj SSChampion Points: 11297 More actions · Answer 2

sounds like you may need something like a pivot table query

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

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

graham 47698 (10/19/2012)
Hi
I have a table which has columns containing sales values for each month for example..
Id, versionId, jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun
The 'versionId' joins to the period table which has the end dates for each month
versionId, juldate,augdate,sepdate,octdate,novdate,decdate,jandate,febdate,madater,aprdate,maydate,jundate
What i need to do is return individual rows for each month with the corresponding period date..
Id
versionId
salesvalue
salesenddate
I have been looking at Multi-Statement Table-Value Functions but haven't been able to get anywhere near this result..
Any assistance greatly appreciated..

As previously stated this does sound like a PIVOT or a cross tab. If you want some help you need to provide enough information so we can help. Please read the article on best practices found at the first link in my signature.

_______________________________________________________________

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/

CapnHector SSCoach Points: 16143 More actions · Answer 4

actually sounds more like an unpivot to normalize a data structure.

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx at the very bottom is the unpivot

EDIT Forgot url tags

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]