none
SQL Help: One column spilt into multiple columns RRS feed

  • Question

  • Hi all,

    I have the following table:

    DECLARE @TypeMax TABLE
    (
      MaxCol nvarchar(max)
    )

    INSERT INTO @TypeMax
    VALUES ('{"ID":"075405435453.doc","Name":
    "Scenario1","Date":"2016-12-19 23:01:03","Type":"SL"}')

    , ('{"ID":"55453450480003.wav","Name":
    "Change43","Date":"2016-12-13 21:03:23","Type":"AL"}')


    Which results in the following:

    {"ID":"075405435453.doc","Name":
    "Scenario1","Date":"2016-12-19 23:01:03","Type":"SL"}
    {"ID":"55453450480003.wav","Name":
    "Change43","Date":"2016-12-13 21:03:23","Type":"AL"}

    What I want to do is spilt out columns ID, Name, Date, Type into rows  like below format - is this possible?

    ID                                  Name           Date                             Type
    075405435453.doc       Scenario1     2016-12-19 23:01:03  SL
    55453450480003.wav  Change43     2016-12-13 21:03:23  AL


    • Edited by Milli_22 Monday, August 27, 2018 10:12 PM
    Tuesday, February 27, 2018 10:38 PM

All replies

  • Is this data format JSON?

    A Fan of SSIS, SSRS and SSAS

    Tuesday, February 27, 2018 10:42 PM
  • SQL Server certainly thinks it is. :) Thank you pointing that out.

    WITH
    	TypeMax(MaxCol)
    AS
    	(
    	 SELECT CONCAT('{"ID":"075405435453.doc","Name":',	CHAR(10), '"Scenario1","Date":"2016-12-19 23:01:03","Type":"SL"}') UNION ALL
    	 SELECT CONCAT('{"ID":"55453450480003.wav","Name":',	CHAR(10), '"Change43","Date":"2016-12-13 21:03:23","Type":"AL"}')
    	)
    SELECT
    	CAST(JSON_VALUE(MaxCol, '$.ID')		AS VARCHAR(20))	Id,
    	CAST(JSON_VALUE(MaxCol, '$.Name')	AS VARCHAR(10))	Name,
    	CAST(JSON_VALUE(MaxCol, '$.Date')	AS DATETIME)	[Date],
    	CAST(JSON_VALUE(MaxCol, '$.Type')	AS VARCHAR(02)) Type
    FROM
    	Typemax
    GO

    Results in:

    Id                   Name       Date                    Type
    -------------------- ---------- ----------------------- ----
    075405435453.doc     Scenario1  2016-12-19 23:01:03.000 SL  
    55453450480003.wav   Change43   2016-12-13 21:03:23.000 AL  


    Wednesday, February 28, 2018 12:14 AM
    Moderator
  • Unfortunately I am using SQL Server 2012 and i get:

    Msg 195, Level 15, State 10, Line 9
    'JSON_VALUE' is not a recognized built-in function name.

    Wednesday, February 28, 2018 12:39 AM
  • Oh, that's too bad. The JSON Functions were added in 2016.

    There are plenty of ways to split strings, but JSON is bound to be complex. It can get a lot easier if we know there will not be any commas or double-quotation marks in the data and that it only includes scalar values as opposed to subsets. If so, it's only a matter of grabbing the substring from 'ID:", " until the next ".

    Wednesday, February 28, 2018 1:49 AM
    Moderator
  • Yeah, only SQL 2016 or higher supports JSON. Try this:

    DECLARE @TypeMax TABLE
    (
    	MaxCol nvarchar(max)
    );
    
    INSERT INTO @TypeMax
    VALUES 
    ('{"ID":"075405435453.doc","Name":"Scenario1","Date":"2016-12-19 23:01:03","Type":"SL"}'), 
    ('{"ID":"55453450480003.wav","Name":"Change43","Date":"2016-12-13 21:03:23","Type":"AL"}');
    
    SELECT 
    SUBSTRING(
    	MaxCol, 
    	CHARINDEX('"ID":"', MaxCol) + LEN('"ID":"'), 
    	CHARINDEX('"Name":"', MaxCol) - CHARINDEX('"ID":"', MaxCol) - LEN('"Name":"')
    ) AS [ID],
    SUBSTRING(
    	MaxCol, 
    	CHARINDEX('"Name":"', MaxCol) + LEN('"Name":"'), 
    	CHARINDEX('"Date":"', MaxCol) - CHARINDEX('"Name":"', MaxCol) - LEN('"Date":"') - 2
    ) AS [Name], 
    SUBSTRING(
    	MaxCol, 
    	CHARINDEX('"Date":"', MaxCol) + LEN('"Date":"'), 
    	CHARINDEX('"Type":"', MaxCol) - CHARINDEX('"Date":"', MaxCol) - LEN('"Date":"') - 2
    ) AS [Date],
    SUBSTRING(
    	MaxCol, 
    	CHARINDEX('"Type":"', MaxCol) + LEN('"Type":"'), 
    	CHARINDEX('"}', MaxCol) - CHARINDEX('"Type":"', MaxCol) - LEN('"Type":"')
    ) AS [Type]
    FROM @TypeMax;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, February 28, 2018 1:50 AM
  • seems like when I change the column name from ID to alertID, Name to SceanrioName the results are not exported correctly
    it misses out the values.

    This is what i get when i change the column names:

    ffdds5405435453. Scenario1","Date"
    Wednesday, February 28, 2018 10:47 AM
  • When you change the column names you need to change those names in the script as well.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, February 28, 2018 2:24 PM
  • DECLARE @TypeMax TABLE
    (
      MaxCol nvarchar(max)
    )
    
    INSERT INTO @TypeMax
    VALUES ('{"ID":"075405435453.doc","Name":"Scenario1","Date":"2016-12-19 23:01:03","Type":"SL"}')
    
    , ('{"ID":"55453450480003.wav","Name":"Change43","Date":"2016-12-13 21:03:23","Type":"AL"}')
    
    --SELECT * FROM @TypeMax
    
    ;With mycte as (
    SELECT     Cast(N'<H><r ' + Replace(Replace(Replace(Replace(Replace( MaxCol , '{"',''), '}',''), '","','" '),'":"','="')  + '" /></H>' ,'""','"' )  as xml)  AS [vals]
    FROM   @TypeMax
    )
     
    ,mycte1 as (
    SELECT  ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn,
    S.a.value('@ID', 'varchar(50)') as [ID], 
    S.a.value('@Name', 'varchar(50)') as [Name],
    S.a.value('@Date', 'varchar(50)') as [Date], 
    S.a.value('@Type', 'varchar(50)') as [Type]  
        
    FROM mycte d CROSS APPLY d.[vals].nodes('/H/r') S(a)  ) 
     
     select ID,Name,[Date],[Type] from mycte1
     /*
     ID	Name	Date	Type
    075405435453.doc	Scenario1	2016-12-19 23:01:03	SL
    55453450480003.wav	Change43	2016-12-13 21:03:23	AL
     */

    Wednesday, February 28, 2018 2:51 PM
    Moderator