Solved

break one row into many...

Posted on 2013-10-24
6
227 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 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 49

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 49

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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
LVL 49

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 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 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