Solved

break one row into many...

Posted on 2013-10-24
6
221 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

864 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

16 Experts available now in Live!

Get 1:1 Help Now