Solved

break one row into many...

Posted on 2013-10-24
6
225 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add a step to a system backup job 6 36
SQL Query--is not excluding a segment of my data 4 18
SQL Query Syntax error after > 11 41
How to trim a value in SQL 2 25
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.​
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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