SQL Server in master: Application of Spt_values

Today, when doing data analysis report, encountered a problem like this.

The table structure is as follows.
Department code, department name, department personnel ID (separated by commas)

650) this.width=650; "title=" 1.jpg "src=" Http://s3.51cto.com/wyfs02/M00/49/B0/wKiom1QZL0fyofCQAAJXUFPFIdk500.jpg " alt= "Wkiom1qzl0fyofcqaajxufpfidk500.jpg"/>

I want to check out a new dataset by linking to the people table and query the result set format as follows:
Person information (ID or name), department code, department name

 

Previously, a new collection field was formed through the program traversal of the Split table fields, and then the result set was queried in conjunction with the SQL statement, but this report requirement can only be implemented by the SQL statement, which was previously remembered by the write segment function and the combination of cursors. Today, however, there is no way to find a new method on the Internet. With "Master." Spt_values "To achieve, concrete implementation method see below Example 1 feel this thing is too good to use. The online examples have been collated, I hope that the great God of criticism and advice, but also hope that we continue to apply this aspect of the application to paste on

Select number from Master. Spt_values with (nolock) where type= ' P '
/** Explanation: Master. The corresponding Number field value for the Spt_values table's field value is P is from 0-2047*/
-----------
--1. Converting a string to a column display
If object_id (' TB ') is not null drop table TB
Go
CREATE table TB ([number] varchar (3), [Product] varchar (2), [quantity] int,[unit Price] int,[amount] int,[serial number] varchar (8))
INSERT into TB ([number],[product],[quantity],[Unit price],[amount],[serial number])
Select ' 001 ', ' AA ', 3,5,15, ' 12,13,14 ' UNION ALL
Select ' 002 ', ' BB ', 8,9,13, ' 22,23,24 '
Go
SELECT [Ref.],[product],[Quantity],[Unit price],[amount]
, substring ([serial number],b.number,charindex (', ', [serial number]+ ', ', B.number)-b.number) as [serial number]
From TB A with (NOLOCK), Master: Spt_values B with (NOLOCK)
where B.number>=1 and B.number<len (a.[serial number]) and b.type= ' P '
and substring (', ' +[serial number],number,1) = ', '
Go
DROP table TB
Go
/**
Number Product quantity Unit price amount serial number
---- ---- ----------- ----------- ----------- --------
001 AA 3 5 15 12
001 AA 3 5 15 13
001 AA 3 5 15 14
002 BB 8 9 13 22
002 BB 8 9 13 23
002 BB 8 9 13 24
*/
----------
--2. A string before the fourth comma
DECLARE @str varchar (100)
Set @str = ' 10,102,10254,103265,541,2154,41,156 '
; with CTE as (
Select Left (@str, number-1) as Ss,row_number () up (order by GETDATE ()) as XH
From Master. Spt_values with (NOLOCK)
where Number>=1 and Number<=len (@str + ', ') and type= ' P '
and substring (@str + ', ', number,1) = ', '
) Select SS from CTE where xh=4
/**
Ss
-------------------
10,102,10254,103265
*/
----------
--3. Find the same kanji in two sentences
DECLARE @Lctext1 varchar (100)
DECLARE @Lctext2 varchar (100)
Set @Lctext1 = ' We are all friends from all corners '
Set @Lctext2 = ' Friends are a lot of road really good to go? '
Select substring (@Lctext2, number,1) as value
From Master. Spt_values with (NOLOCK)
where type= ' P ' and Number>=1 and Number<=len (@Lctext2)
and CHARINDEX (substring (@Lctext2, number,1), @Lctext1, number) >1
/**
Value
-----
Friends
Friends
Of
*/
---------
--4. Extracting all the months between two dates
If object_id (' TB ') is not null drop table TB
Go
CREATE table TB (ID int identity (max), StartDate varchar), endDate varchar (10))
INSERT into TB (startdate,enddate) Select ' 2013-01-01 ', ' 2013-09-25 '
Go
DECLARE @startDate varchar (10)
DECLARE @endDate varchar (10)
Select @startDate =startdate, @endDate =enddate from TB with (NOLOCK)
Select CONVERT (varchar (7), DateAdd (Mm,number, @startDate), as [month]
From Master. Spt_values with (NOLOCK)
where type= ' P ' and number>=0
and DateAdd (Mm,number, @startDate) <[email protected]
Go
DROP table TB
Go
/**
Month
-------
2013-01
2013-02
2013-03
2013-04
2013-05
2013-06
2013-07
2013-08
2013-09
*/
---------
--5. Find all dates for the month in which the date is located
DECLARE @date datetime
Set @date = ' 2013-08-31 '
Select CONVERT (char (7), @date, +) + '-' +right (' 0 ' +convert (varchar (2), number), 2) as [date format 1]
, LTrim (Year (@date)) +right (100+month (@date), 2) +right (' 0 ' +ltrim (number), 2) as [date format 2]
From Master. Spt_values with (NOLOCK)
where type= ' P ' and number>=1
--and Number<=datediff (DD, @date, DATEADD (mm,1, @date))--for MSSQL, this statement is not tried in 2013-08-31, when the number of days is 30 days because there is no number 31st in September
and Number<=datediff (char (7), @date, Dd,convert) + ' -01 ', convert (char (7), DateAdd (mm,1, @date), 120) + '-01 ')-- Convert to number 1th to calculate the number of days
/**
Date format 1st period format 2
----------- --------------------
2013-08-01 on 20130801
2013-08-02 on 20130802
2013-08-03 on 20130803
2013-08-04 on 20130804
2013-08-05 on 20130805
2013-08-06 on 20130806
2013-08-07 on 20130807
2013-08-08 on 20130808
2013-08-09 on 20130809
2013-08-10 on 20130810
2013-08-11 on 20130811
2013-08-12 on 20130812
2013-08-13 on 20130813
2013-08-14 on 20130814
2013-08-15 on 20130815
2013-08-16 on 20130816
2013-08-17 on 20130817
2013-08-18 on 20130818
2013-08-19 on 20130819
2013-08-20 on 20130820
2013-08-21 on 20130821
2013-08-22 on 20130822
2013-08-23 on 20130823
2013-08-24 on 20130824
2013-08-25 on 20130825
2013-08-26 on 20130826
2013-08-27 on 20130827
2013-08-28 on 20130828
2013-08-29 on 20130829
2013-08-30 on 20130830
2013-08-31 on 20130831
*/
---------
--6. A time period divided by 2 hours based on a given time base
DECLARE @time varchar (5)
Set @time = ' 11:13 '
Select LTrim (A.number) +right (@time, 3) + '-' +ltrim (b.number) +right (@time, 3) as [dividing result]
From Master. Spt_values A With (NOLOCK), master. Spt_values B with (NOLOCK)
Where a.type= ' P ' and b.type= ' P '
and A.number>=left (@time, 2) and b.number<=24
and A.number+2=b.number
/**
Dividing results
-----------------------------------
11:13-13:13
12:13-14:13
13:13-15:13
14:13-16:13
15:13-17:13
16:13-18:13
17:13-19:13
18:13-20:13
19:13-21:13
20:13-22:13
21:13-23:13
22:13-24:13
*/
---------
--7. displaying strings as rows and columns
If object_id (' TB ') is not null drop table TB
CREATE table TB (ID int identity (), S nvarchar (100))
INSERT into TB (s) SELECT ' parking address 1, parking condition 1| parking address 2, parking space 2| parking address n, parking condition n '
; with CTE as (
Select substring (s,number,charindex (' | ', s+ ' | ', number)-number) as SS
From TB with (NOLOCK), Master: Spt_values with (NOLOCK)
where type= ' P ' and Number>=1 and Number<=len (s)
and substring (' | ') +s,number,1) = ' | '
) Select Left (Ss,charindex (', ', ss)-1) as s1,substring (Ss,charindex (', ', ss) +1,len (ss)) as S2 from CTE
DROP table TB
/**
S1 s2
----------- ------------
Parking address 1 parking spaces 1
Parking Address 2 parking spaces 2
Parking address n parking space n
*/

This article is from the "Rain Wandering Blog" blog, please be sure to keep this source http://101779.blog.51cto.com/91779/1554325

SQL Server in master: Application of Spt_values