Friday, 15 July 2011

Convert a single column to multiple in SQL Server -


I have a column of data in SQL Saver and it needs to be expanded to multiple (2) columns.

Raw data looks like this:

  Call 1 ------- Monday John Boris Natalie Tuesday James Tom Boris   

I want to see it as:

  name | Day ------- + --------- John | Monday Boris | Monday Natalie | Monday James | Tom Tom | Tuesday Boris | Tuesday   

Many thanks in advance.

Assume that you are going to order that identity column to add that table to To be able to add and that your table will look something like this:

  create table table 1 (id int identity (1,1), call 1 nvarar (50 ) 'Table 1 (call1) value (' Monday '), (' zone '), (' Boris'), ('Natalie'), ('Tuesday'), ('James'), (' Tom ') Include in ('Boris')   

You can try the following:

  • Create a temporary table for the first working day so that you can isolate them (this can also be done
  • The original table in search of large ID (TID> D.ID) Join the day first
  • Prevent those who are id (tid. Lt; MIN (d2.Id)) the next day
  • Only to ensure that it The list works for the last day.

      Create Table #weekDays (wd NVARCHAR (10)); INSERT #weekDays VALUES ('Monday '), (' Tuesday '), (' Wed ' War '), (' Thursday '), (' Friday '), (' Saturday '), (' Sunday '); As with CTE_Days (SELECT t. * Table 1T from INNER wd.wd = t.col1 wd on wddays wd) SELECT * to CTE_Days d1 INNER T1id Join table 1t & gt; D1.id and t.id & lt; COALESCE ((SELECT MIN (d2.id) CTE_Days D2 WHERE d2.id> d1.id), t.id + 1)    

No comments:

Post a Comment