Solved

break one row into many...

Posted on 2013-10-24
6
219 Views
Last Modified: 2013-11-04
Current data format like:
col1 col2 col3 col4 Col5 (Row 1)
Col6 COl7 Col8 Col9 Col10 (Row 2)
Col11 COl12 Col13 Col14 Col15 (Row 3)

result should be:
col2
      Col3
      Col4
      Col5

Col 7
      Col8
      Col9
      Col10

Col 12
      Col13
      Col14
      Col15

First column (col1/col6/col11 etc) is not displayed/skipped in output.
what sql syntax can help the transformation?
0
Comment
Question by:25112
  • 4
6 Comments
 
LVL 5

Assisted Solution

by:MohitPandit
MohitPandit earned 125 total points
ID: 39596890
Hello,

Please find below table valued function as below:

CREATE FUNCTION ParseString_fn       
(      
	 @String VarChar(MAX),      
	 @Delimitor VarChar(5)      
      
)
RETURNS @Result TABLE (Id Int IDENTITY PRIMARY KEY, Value VarChar(8000))   
AS      
BEGIN
	
	 DECLARE @Count Int -- For get location for delimitor
	 SELECT @Count = CHARINDEX (@Delimitor, @String , 1)       
     
    -- Check here if we don't have delimitor location then insert whole string in one go and return
	IF @Count = 0      
	BEGIN      
		INSERT INTO @Result      
		VALUES (@String)      
		RETURN      
	END      
      
    -- Check here if delimitor is null or didn't pass then return
	IF @Delimitor IS NULL OR LEN(@Delimitor) <= 0      
	BEGIN      
		RETURN    
	END       
      
    -- Insert first column here.
	INSERT INTO @Result      
	SELECT SUBSTRING (@String, 1, @Count - 1)      
    
    -- While loop untill found passed delimitor
	WHILE CHARINDEX (@Delimitor, @String, @Count + 1) > 0       
	BEGIN      
		INSERT INTO @Result      
		SELECT SUBSTRING (@String, @Count + 1, CHARINDEX (@Delimitor, @String, @Count + 1) - @Count -1)      
		  
		SELECT @Count = CHARINDEX (@Delimitor, @String, @Count + 1)       
	END      
    
    -- Insert last column value
	INSERT INTO @Result      
	SELECT SUBSTRING (@String, @Count + 1, LEN(@String) - @Count)      
 RETURN      
END

Open in new window


How to execute
CREATE TABLE #T1_Input(Row VarChar(100))
CREATE TABLE #T1_Result (Result VarChar(50))
CREATE TABLE #T1_Input_Temp (Id Int IDENTITY PRIMARY KEY, Row VarChar(100))
DECLARE 
	@Row_Num Int = 1, @Row_Count Int = 0, @Row VarChar(100)

INSERT INTO #T1_Input (Row)
VALUES ('col1 col2 col3 col4 Col5'), ('Col6 COl7 Col8 Col9 Col10'), ('Col11 COl12 Col13 Col14 Col15')

INSERT INTO #T1_Input_Temp (Row)
SELECT Row FROM #T1_Input
SET @Row_Count = @@ROWCOUNT


IF (@Row_Count > 0)
BEGIN
	WHILE (@Row_Count > 0)
	BEGIN
		SELECT
			@Row = Row
		FROM #T1_Input_Temp
		WHERE Id = @Row_Num
		
		INSERT INTO #T1_Result (Result)
		SELECT 
			CASE 
				WHEN Id = 1 THEN Value 
			ELSE
				SPACE(3) + Value
			END
		FROM ParseString_fn(REPLACE(SUBSTRING(@Row, CHARINDEX(' ',@Row,1)+1, LEN(@Row)),' ', ','), ',')
		
		-- Increment & decrement here
		SET @Row_Count = @Row_Count - 1
		SET @Row_Num = @Row_Num + 1
	END
END

SELECT * FROM #T1_Result

DROP TABLE #T1_Input
DROP TABLE #T1_Result
DROP TABLE #T1_Input_Temp

Open in new window


Please note, first you need to execute table valued function and after that temporary execution.
Also, please find below reference
#T1_Input --> should have your physical table
#T1_Result --> It will store final result
#T1_Input_Temp --> It will assist as mediator for iteration.

Kindly take a look over it and let me know in case you have any concern.

Best Regards,
Mohit Sharma
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 375 total points
ID: 39596977
Not sure how you got to the existing 3 row layout, but you could go from a single row source to a single column like the folowing - and I have excluded 'Col5' and 'Col15' for this example:
    CREATE TABLE YourTable
    	([ID] int, [Col1] varchar(5), [Col2] varchar(5), [Col3] varchar(5), [Col4] varchar(5), [Col5] varchar(5), [Col6] varchar(5), [Col7] varchar(5), [Col8] varchar(5), [Col9] varchar(5), [Col10] varchar(6), [Col11] varchar(6), [Col12] varchar(6), [Col13] varchar(6), [Col14] varchar(6), [Col15] varchar(6), [Col16] varchar(6), [Col17] varchar(6), [Col18] varchar(6), [Col19] varchar(6), [Col20] varchar(6))
    ;
    	
    INSERT INTO YourTable
    	([ID], [Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10], [Col11], [Col12], [Col13], [Col14], [Col15], [Col16], [Col17], [Col18], [Col19], [Col20])
    VALUES
    	(1, '1Val1', '1Val2', '1Val3', '1Val4', '1Val5', '1Val6', '1Val7', '1Val8', '1Val9', '1Val10', '1Val11', '1Val12', '1Val13', '1Val14', '1Val15', '1Val16', '1Val17', '1Val18', '1Val19', '1Val20'),
    	(2, '2val1', '2val2', '2val3', '2val4', '2val5', '2val6', '2val7', '2val8', '2val9', '2val10', '2val11', '2val12', '2val13', '2val14', '2val15', '2val16', '2val17', '2val18', '2val19', '2val20')
    ;

**Query 1**:

    select
            id
          , ColsAsRows
    from YourTable
    cross apply (
                  values
                         (convert(nvarchar(max),col1))
                       , (convert(nvarchar(max),col2))
                       , (convert(nvarchar(max),col3))
                       , (convert(nvarchar(max),col4))
                       , (convert(nvarchar(max),col6))
                       , (convert(nvarchar(max),col7))
                       , (convert(nvarchar(max),col8))
                       , (convert(nvarchar(max),col9))
                       , (convert(nvarchar(max),col10))
                       , (convert(nvarchar(max),col11))
                       , (convert(nvarchar(max),col12))
                       , (convert(nvarchar(max),col13))
                       , (convert(nvarchar(max),col14))
                       , (convert(nvarchar(max),col16))
                       , (convert(nvarchar(max),col17))
                       , (convert(nvarchar(max),col18))
                       , (convert(nvarchar(max),col19))
                       , (convert(nvarchar(max),col20))
                 )as ca1 (ColsAsRows)
    

**[Results][2]**:
    
    | ID | COLSASROWS |
    |----|------------|
    |  1 |      1Val1 |
    |  1 |      1Val2 |
    |  1 |      1Val3 |
    |  1 |      1Val4 |
    |  1 |      1Val6 |
    |  1 |      1Val7 |
    |  1 |      1Val8 |
    |  1 |      1Val9 |
    |  1 |     1Val10 |
    |  1 |     1Val11 |
    |  1 |     1Val12 |
    |  1 |     1Val13 |
    |  1 |     1Val14 |
    |  1 |     1Val16 |
    |  1 |     1Val17 |
    |  1 |     1Val18 |
    |  1 |     1Val19 |
    |  1 |     1Val20 |
    |  2 |      2val1 |
    |  2 |      2val2 |
    |  2 |      2val3 |
    |  2 |      2val4 |
    |  2 |      2val6 |
    |  2 |      2val7 |
    |  2 |      2val8 |
    |  2 |      2val9 |
    |  2 |     2val10 |
    |  2 |     2val11 |
    |  2 |     2val12 |
    |  2 |     2val13 |
    |  2 |     2val14 |
    |  2 |     2val16 |
    |  2 |     2val17 |
    |  2 |     2val18 |
    |  2 |     2val19 |
    |  2 |     2val20 |



  [1]: http://sqlfiddle.com/#!3/ea2d2/2

Open in new window

Note, you face a conversion issue when trying to force different data types into a single column because one column must be one data type. Here I have simply converted all into nvarchar(max)
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 375 total points
ID: 39598950
This result:
| ID | SUBHEADING | DETAILS |
|----|------------|---------|
|  1 |      1Val2 |         |
|  1 |            |   1Val3 |
|  1 |            |   1Val4 |
|  1 |            |   1Val5 |
|  1 |      1Val7 |         |
|  1 |            |   1Val8 |
|  1 |            |   1Val9 |
|  1 |            |  1Val10 |
|  1 |     1Val12 |         |
|  1 |            |  1Val13 |
|  1 |            |  1Val14 |
|  1 |            |  1Val15 |
|  2 |      2val2 |         |
|  2 |            |   2val3 |
|  2 |            |   2val4 |
|  2 |            |   2val5 |
|  2 |      2val7 |         |
|  2 |            |   2val8 |
|  2 |            |   2val9 |
|  2 |            |  2val10 |
|  2 |     2val12 |         |
|  2 |            |  2val13 |
|  2 |            |  2val14 |
|  2 |            |  2val15 |

Open in new window

was produced the following following Query (on assumed table and data):
select
        id
      , SubHeading
      , Details
from YourTable
cross apply (
              values
                     (convert(nvarchar(max),col2) , '')
                   , (''                          , convert(nvarchar(max),col3))
                   , (''                          , convert(nvarchar(max),col4))
                   , (''                          , convert(nvarchar(max),col5))
                   , (convert(nvarchar(max),col7) , '')
                   , (''                          , convert(nvarchar(max),col8))
                   , (''                          , convert(nvarchar(max),col9))
                   , (''                          , convert(nvarchar(max),col10))
                   , (convert(nvarchar(max),col12), '')
                   , (''                          , convert(nvarchar(max),col13))
                   , (''                          , convert(nvarchar(max),col14))
                   , (''                          , convert(nvarchar(max),col15))
)as ca1 (SubHeading, Details)

-- table and data



CREATE TABLE YourTable
    ([ID] int, [Col1] varchar(5), [Col2] varchar(5), [Col3] varchar(5), [Col4] varchar(5), [Col5] varchar(5), [Col6] varchar(5), [Col7] varchar(5), [Col8] varchar(5), [Col9] varchar(5), [Col10] varchar(6), [Col11] varchar(6), [Col12] varchar(6), [Col13] varchar(6), [Col14] varchar(6), [Col15] varchar(6), [Col16] varchar(6), [Col17] varchar(6), [Col18] varchar(6), [Col19] varchar(6), [Col20] varchar(6))
;
    
INSERT INTO YourTable
    ([ID], [Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10], [Col11], [Col12], [Col13], [Col14], [Col15], [Col16], [Col17], [Col18], [Col19], [Col20])
VALUES
    (1, '1Val1', '1Val2', '1Val3', '1Val4', '1Val5', '1Val6', '1Val7', '1Val8', '1Val9', '1Val10', '1Val11', '1Val12', '1Val13', '1Val14', '1Val15', '1Val16', '1Val17', '1Val18', '1Val19', '1Val20'),
    (2, '2val1', '2val2', '2val3', '2val4', '2val5', '2val6', '2val7', '2val8', '2val9', '2val10', '2val11', '2val12', '2val13', '2val14', '2val15', '2val16', '2val17', '2val18', '2val19', '2val20')
;

: http://sqlfiddle.com/#!3/ea2d2/10 

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 375 total points
ID: 39598997
By the way, this style of data manipulation appears to be purely for the purposes of "presentation" (e.g. a report).

SQL is not a report writer, and usually this type of presentation is better handled by your business intelligence product.

For example, if you removed the column ID from the result I display immediately above:
How would you order the output? i.e. you may need something like ID in every row to help provide a consistent order.

And:
That existing 3 row output is going to be a substantial problem as it too isn't a "natural thing" for SQL to handle. I'm assuming you have done this for presentation purposes also.

I'd suggest going back to a more conventional method of having the wanted data in a single row, then use a "presentation technique" to display those 3 rows. Or, if those 3 rows were a stepping stone you took toward reaching the Sub-heading/Detail layout of this question then I would stop using that approach.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39611087
Hi, any feedback on the propose answers?

Just wondering if you need further explanation.
0
 
LVL 5

Author Comment

by:25112
ID: 39622424
i have digested on your solution. thanks Paul & Mohit ..

i am working on deploying some and testing. thanks again.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now