Learn MVC (Model view controller) Step by Step in 7 days – Day 1

http://www.dotnetspider.com/resources/42969-Learn-MVC-Model-view-controller-Step-by-Step.aspx

spilt more than one value

CREATE TABLE #temp(
[Day] [varchar](500) NULL,
[RawKey] [int] NULL,
[TableID] [int] NULL,
[FromTime] [varchar](500) NULL
)
insert into #temp
select 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday', 1, 1, '10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00'


;WITH CTE1 AS
(
SELECT *
,RN= Row_Number() OVER( Partition BY [Day],[FromTime],RawKey,TableID ORDER BY TableID)
FROM
(
SELECT *
,DelimitedStringXML = CAST('<d>'+REPLACE([Day],',','</d><d>')+'</d>' AS XML)
,DelimitedValueXML = CAST('<d>'+REPLACE([FromTime],',','</d><d>')+'</d>' AS XML)

FROM #temp
) as t
Cross Apply
(
SELECT y.value('.', 'VARCHAR(30)') AS SplitString FROM DelimitedStringXML.nodes('//d') as  x(y)

) as b
)
,CTE2 AS
(
SELECT *
,RN= Row_Number() OVER( Partition BY [Day],[FromTime],RawKey,TableID ORDER BY TableID)
FROM
(
SELECT *
,DelimitedStringXML = CAST('<d>'+REPLACE([Day],',','</d><d>')+'</d>' AS XML)
,DelimitedValueXML = CAST('<d>'+REPLACE([FromTime],',','</d><d>')+'</d>' AS XML)

FROM #temp
) as t
CROSS APPLY
(
SELECT h.value('.', 'VARCHAR(30)') AS SplitValue FROM DelimitedValueXML.nodes('//d') as  g(h)

) as c
)


SELECT a.RawKey,a.SplitString,b.SplitValue
FROM CTE1 as a
INNER JOIN CTE2 as b
on  a.TableID= b.TableID
AND a.RN = b.RN

drop table #temp

SQL Server CROSS APPLY and OUTER APPLY

http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

T SQL Script - Splitting a delimited column value to multiple columns

Create Table Product(Col1 varchar(10),Col2 Varchar(1000))

iNSERT INTO pRODUCT(Col1,Col2) sELECT 1,'AR-5381 BA-8327 BB-7421 BB-8107 BB-9108'
iNSERT INTO pRODUCT(Col1,Col2) sELECT 2,'BC-M005 BC-R205 BE-2349 BE-2908 BK-M18B-40'

   
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT Col1, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5
FROM
(SELECT Col1,
        ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY nums.n) AS PositionInList,
        LTRIM(RTRIM(SUBSTRING(valueTable.Col2, nums.n, charindex(N' ', valueTable.Col2 + N' ', nums.n) - nums.n))) AS [Value]
 FROM   Numbers AS nums INNER JOIN Product AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col2)) AND SUBSTRING(N' ' + valueTable.Col2, n, 1) = N' ') AS SourceTable
PIVOT
(
MAX([VALUE]) FOR PositionInList IN ([1], [2], [3], [4], [5])
) AS Table2

Drop table Product

Split one long comma string into multi column new table

CREATE TABLE Table1
([Field1] varchar(38))
;

INSERT INTO Table1
([Field1])
VALUES
('Apples,oranges, pears, berries, melons')
;

WITH Split_Fields (Field, xmlfields)
AS
(
    SELECT Field1 AS Field,
    CONVERT(XML,'<Fields><field>'
    + REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM Table1
)

 SELECT Field,    
 xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1,  
 xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2,
 xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3,  
 xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4,
 xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
 FROM Split_Fields

SQL How to Split One Column WIth 2 Different Delimiters into Multiple Variable Columns

create table #MessyDelim
(DelimList varchar(255));

insert into #MessyDelim
Values ('30;120;100')
, ('50;60')
, ('75/10')
, ('115/50/20/10/5')
, ('80;65;40;23;12;10')
, ('100')
;WITH
MessyDelim AS --As Nenad Zivkovic suggested, standardize on one delimiter for simplicity
(   SELECT DelimList,REPLACE(DelimList,'/',';') AS String
    FROM #MessyDelim),
Split AS --Recursive CTE to produce strings each with one less delimited value attached
(   SELECT DelimList, 1 AS Sort, String + ';' AS String
    FROM MessyDelim
    UNION ALL
    SELECT DelimList, Sort+1, RIGHT(String,LEN(String)-CHARINDEX(';',String))
    FROM Split
    WHERE CHARINDEX(';',String) > 0 ),
Cleanup AS --Reduce strings to single delimited value each
(   SELECT DelimList,LEFT(String,CHARINDEX(';',String)-1) AS String, Sort
    FROM Split
    WHERE String <> '' )  
SELECT DelimList, --Pivot out into columns
    [1] AS DL1,
    [2] AS DL2,
    [3] AS DL3,
    [4] AS DL4,
    [5] AS DL5,
    [6] AS DL6
FROM Cleanup
PIVOT(MAX(String) FOR Sort IN ([1],[2],[3],[4],[5],[6])) pvt
ORDER BY DelimList
OPTION (MAXRECURSION 10) --Just for safety sake

SQL SERVER – Remove Duplicate Entry from Comma Delimited String – UDF

CREATE FUNCTION dbo.DistinctList(@List VARCHAR(MAX),@Delim CHAR)RETURNS
VARCHAR(MAX)AS
BEGIN
DECLARE @ParsedList TABLE(Item VARCHAR(MAX)
)DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)SET @list = LTRIM(RTRIM(@list)) + @DelimSET @pos = CHARINDEX(@delim, @list, 1)WHILE @pos > 0BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))IF @list1 <> ''INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))SET @list = SUBSTRING(@list, @pos+1, LEN(@list))SET @pos = CHARINDEX(@delim, @list, 1)END
SELECT @rlist = COALESCE(@rlist+',','') + itemFROM (SELECT DISTINCT Item FROM @ParsedList) tRETURN @rlistENDGOSELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',')DistinctList
GO