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 in the following format: personnel 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.

Press CTRL + C to copy the codeSelect number from Master. Spt_values with (nolock) where type= ' P '/** explained: Master. The corresponding Number field value for the Spt_values table's field value is p from 0-2047*/--1. Converting a string to a column shows 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 allselect ' 002 ', ' BB ', 8,9,13, ' 22,23,24 ' Go SELECT [number],[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------------------------------- ------------------AA 3 5 AA 3 5 AA 3 5 BB 8 9 BB 8 9 BB 8 9 */------------2. The string before the fourth comma declare @st R varchar Set @str = ' 10,102,10254,103265,541,2154,41,156 '; with CTE as (select Left (@str, number-1) as Ss,row_number ( ) Over (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 Chinese characters in two sentences declare @Lctext1 varchar (DECLARE) @Lctext2 varchar Set @Lctext1 = ' We are all friends from all corners ' set @Lctext2 = ' Friends are way 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-----Friend's */-----------4. Extract all months between two dates if object_id (' TB ') is not null Dr OP table TB Go CREATE TABLE TB (ID int identity (startdate), varchar(ten), endDate varchar) INSERT into TB (startdate,enddate) Select ' 2013-01-01 ', ' 2013-09-25 ' go declare @startDate varchar (DECLARE) @endDate varchar () 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) <= @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 days of the month in which the date is located Period 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))-- In the case of MSSQL, the statement was not tried in 2013-08-31, and since September there is no number 31st, solid calculated days are 30 days and Number<=datediff (Dd,convert (char (7), @date, 120) + ' -01 ', convert (char (7), DateAdd (mm,1, @date), 120) + '-01 ')--Convert to number 1th to calculate days/** date format 1st period format 2----------------------------- --2013-08-01 20130801 2013-08-02 20130802 2013-08-03 20130803 2013-08-04 20130804 2013-08-05 20130805 2013-08-06 20 130806 2013-08-07 20130807 2013-08-08 20130808 2013-08-09 20130809 2013-08-10 20130810 2013-08-11 20130811 2013-08-12 20130812 2013-08-13 20130813 2013-08-14 20130814 2013-08-15 20130815 2013-08-16 20130816 2013-08-17 20130817 2013-0 8-18 20130818 2013-08-19 20130819 2013-08-20 20130820 2013-08-21 20130821 2013-08-22 20130822 2013-08-23 20130823 20 13-08-24 20130824 2013-08-25 20130825 2013-08-26 20130826 2013-08-27 20130827 2013-08-28 20130828 2013-08-29 2013082 9 2013-08-30 20130830 2013-08-31 20130831 */-----------6. The time period divided by the day is divided by 2 hours based on the given time declare @time varchar (5) Set @time = ' 11:13 ' select LTrim (a.number) +right (@time, 3) + '-' +ltrim (b.number) +right (@time, 3) as [partition 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/** Partitioning 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. Display string as column if Obje ct_id (' TB ') is not null drop table TB CREATE TABLE TB (ID int identity (), s nvarchar (+)) insert into TB (s) SELECT ' Parking address ' 1, parking conditions 1| parking address 2, parking conditions 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 spaces 1 parking spaces 1 parking spaces 2 parking spaces 2 parking spaces n * *Press CTRL + C to copy the code

SQL Server in master: Application of Spt_values