Split function help - 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 2008 Forums

Transact-SQL (2008)

Split function help

Author

Topic

Indsqlbeginner
Starting Member

21 Posts

Posted - 2014-06-30 : 02:28:49

Dear All

please help me on the below



Current Query Input :

Quarter                Month                      Year
Q1Y16                   4,5,6                      2015
Q2Y15                   7,8,9                      2014
Q2Y16                   7,8,9                      2015
Q3Y15                   10,11,12                   2014
Q4Y15                   1,2,3                      2015

Desired Output


Quarter                Month                           Year
Q1Y16                   4                              2015
Q1Y16                   5                              2015
Q1Y16                   6                              2015
Q2Y15                   7                              2014
Q2Y15                   8                              2014
Q2Y15                   9                              2014
Q2Y16                   7                              2015
Q2Y16                   8                              2015
Q2Y16                   9                              2015
Q3Y15                   10                             2014
Q3Y15                   11                             2014
Q3Y15                   12                             2014
Q4Y15                   1                              2015
Q4Y15                   2                              2015
Q4Y15                   3                              2015

Thanks in Advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-30 : 05:11:51

[code]DECLARE @Sample TABLE
(
[Quarter] CHAR(5) NOT NULL,
[Month] VARCHAR(8) NOT NULL,
[Year] SMALLINT NOT NULL
);

INSERT @Sample
(
[Quarter],
[Month],
[Year]
)
VALUES ('Q1Y16', '4,5,6' , 2015),
('Q2Y15', '7,8,9' , 2014),
('Q2Y16', '7,8,9' , 2015),
('Q3Y15', '10,11,12' , 2014),
('Q4Y15', '1,2,3' , 2015);

-- SwePeso
WITH cteSource([Quarter], [Month], [Year])
AS (
SELECT [Quarter],
REPLACE([Month], ',', '.') AS [Month],
[Year]
FROM @Sample
)
SELECT s.[Quarter],
f.[Month],
s.[Year]
FROM cteSource AS s
CROSS APPLY (
VALUES (CAST(PARSENAME(s.[Month], 3) AS TINYINT)),
(CAST(PARSENAME(s.[Month], 2) AS TINYINT)),
(CAST(PARSENAME(s.[Month], 1) AS TINYINT))
) AS f([Month]);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources