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)

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 are collated, I hope that the great God of criticism and advice, but also hope that we continue to apply this aspect of the application.

SelectNumber fromMaster.. Spt_values with (NOLOCK)whereType='P'/** Explanation: Master. The corresponding Number field value for the Spt_values table's field value is P is from 0-2047*/--1. Convert a string to a column displayifOBJECT_ID ('TB') isNotNULLdrop 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, the,'12,13,14'Union AllSelect '002','BB',8,9, -,'22,23,24'GoSelect[number],[product],[quantity],[Unit price],[amount], substring ([serial number],b.number,charindex (',', [Serial number]+',', B.number)-b.number) as[serial number] fromTB A with (NOLOCK), Master: Spt_values B with (NOLOCK)whereb.number>=1and 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 001 AA 3 5 001 AA 3 5 1   5 002 BB 8 9 002 bb 8 9 002 bb 8 9*/----------   --2the string before the fourth comma declare @str varchar ( -) Set@str ='10,102,10254,103265,541,2154,41,156'; with CTE as( SelectLeft (@str, number-1) asSs,row_number () over (order by GETDATE ()) asXH fromMaster.. Spt_values with (NOLOCK)wherenumber>=1and Number<=len (@str +',') and type='P'and substring (@str+',', number,1)=',')SelectSs fromCtewhereXh=4 /** SS-------------------10,102,10254,103265*/----------     --3find the same Chinese characters in two sentences declare @Lctext1 varchar ( -) declare @Lctext2 varchar ( -) Set@Lctext1 ='We're all friends from all corners.'Set@Lctext2 ='a lot of friends, really good way to go?'SelectSUBSTRING (@Lctext2, number,1) asvalue fromMaster.. Spt_values with (NOLOCK)whereType='P'and number>=1and number<=Len (@Lctext2) and charindex (substring (@Lctext2, number,1), @Lctext1, number) >1 /** Value-----Friend's*/---------     --4. Extract all the months between two datesifOBJECT_ID ('TB') isNotNULLdrop table TB Go CREATE TABLE TB (IDintIdentity1,1), StartDate varchar (Ten), EndDate varchar (Ten) ) insert into TB (startdate,enddate)Select '2013-01-01','2013-09-25'Go declare @startDate varchar (Ten) declare @endDate varchar (Ten) Select@startDate =startdate, @endDate =enddate fromTB with (NOLOCK)SelectCONVERT (varchar (7), DateAdd (Mm,number, @startDate), -) as[Month] fromMaster.. Spt_values with (NOLOCK)whereType='P'and number>=0and DateAdd (Mm,number, @startDate)<=@endDate 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 in the month of a date declare @date datetimeSet@date ='2013-08-31'SelectConvertChar(7), @date, -)+'-'+right ('0'+convert (varchar (2), number),2) as[date format 1], LTrim (year (@date))+right ( -+month (@date),2) +right ('0'+ltrim (number),2) as[date format 2] fromMaster.. Spt_values with (NOLOCK)whereType='P'and number>=1--and Number<=datediff (DD, @date, DATEADD (MM,1, @date))--for MSSQL this statement does not try to 2013- ,-31, when there is no 31st in September, the number of days fixed is 30 days and numbers<=datediff (Dd,convert (Char(7), @date, -)+'-01', CONVERT (Char(7), DateAdd (MM,1, @date), -)+'-01')--Convert to number 1th to calculate the number of days/** Date format 1st period format 2-------------------------------2013-08-01 20130801 2013-08-02 20130802 2013-08-03 20130803 20 13-08-04 20130804 2013-08-05 20130805 2013-08-06 20130806 2013-08-07 20130807 2013-08-08 20130808 2013-08-09 2013080 9 2013-08-10 20130810 2013-08-11 20130811 2013-08-12 20130812 2013-08-13 20130813 2013-08-14 20130814 2013-08-15 201  30815 2013-08-16 20130816 2013-08-17 20130817 2013-08-18 20130818 2013-08-19 20130819 2013-08-20 20130820 2013-08-21 20130821 2013-08-22 20130822 2013-08-23 20130823 2013-08-24 20130824 2013-08-25 20130825 2013-08-26 20130826 2013-08 -27 20130827 2013-08-28 20130828 2013-08-29 20130829 2013-08-30 20130830 2013-08-31 20130831*/---------     --6based on a given time, a 2-hour period is divided to derive the period of time divided by one day declare @time varchar (5) Set@time ='11:13'SelectLTrim (A.number) +right (@time,3)+'-'+ltrim (B.number) +right (@time,3) as[dividing result] fromMaster.. Spt_values A With (NOLOCK), master. Spt_values B with (NOLOCK)whereA.type='P'and b.type='P'and A.number>=left (@time,2) and b.number<= -and A.number+2=B.number/** Division 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. To display a string as a row and columnifOBJECT_ID ('TB') isNotNULLdrop table TB CREATE TABLE TB (IDintIdentity1,1), S nvarchar ( -) ) insert into TB (s)Select 'Parking address 1, parking space 1| parking address 2, parking condition 2| parking address n, parking condition n'; with CTE as( SelectSUBSTRING (S,number,charindex ('|', s+'|', number)-number) asSS fromTB with (NOLOCK), Master: Spt_values with (NOLOCK)whereType='P'and number>=1and number<=Len (s) and substring ('|'+s,number,1)='|')SelectLeft (Ss,charindex (',', SS)-1) asS1,substring (Ss,charindex (',', SS) +1, Len (ss)) asS2 fromCTE drop table TB/** S1 s2-----------------------parking spaces 1 parking spaces 1 parking spaces 2 parking spaces 2 parking spaces n*/

SQL Server in master: Application of Spt_values