?
Solved

break one row into many...

Posted on 2013-10-24
6
Medium Priority
?
229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 5

Assisted Solution

by:MohitPandit
MohitPandit earned 500 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1500 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1500 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 49

Accepted Solution

by:
PortletPaul earned 1500 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 49

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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