x

Reg: Merge multiple columns into single column data

Please help me on the below.

My table having the data as

 Column: 1 2 3 4 5
 Data  : 1 1 2 3 3

The result should be like the below as

 Column : |----|-----|----|-----|-----|
 Data   : |  1 |  2  |  3 |  4  |  5  |
          |----|-----|----|-----|-----| 
          |    1     |  2 |     3     |
          |----------|----|-----------|

Thanks and Regards

K.Bildoss

more ▼

asked May 30, 2012 at 06:49 PM in Default

avatar image

Bildoss
0 1 1 1

That's a bit bizarre. Do you want the column names all in the same row as some of the data with pipes and dashes in between, just as you have it presented?

May 30, 2012 at 09:56 PM KenJ

I would say that is as bizarre as it can get ;) On top of that, it is to be done in TSQL.

May 31, 2012 at 05:29 AM Usman Butt

To remove @KenJ & @Usman Butt's confusion, I've reformatted the question so you can see what @Bildoss was really looking for!

May 31, 2012 at 09:09 AM ThomasRushton ♦♦

HI Ken,

I dont want the delimeter and pipe symbol, Since I put the delimeter and pipe symbol for table design. I need only the data like this.

Thanks and Regards K.Bildoss

May 31, 2012 at 10:57 AM Bildoss

HI Usman,

Thanks for the below script. But i need only the data inside the delimeter and pipe. I have put the delimeter and pipe symbol only for table design. No need to process the delimeter and pipe.

May 31, 2012 at 11:04 AM Bildoss
show all comments (comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

This is one of the ways to do it. The highlights of the solution is to

  • Concatenate the column names with '|' as the Delimiter using XML path

  • Concatenate the columns values with '|' as the Delimiter

  • Use the 8k splitter, which originally was modified to gain more performance. An additional change is to return the distinct values with '|' as the delimiter

You should be able to make any presentation change if required.

 --=====================================================================================================================
 --      Do everything in the tempdb.
 --=====================================================================================================================
 USE [tempdb]
 GO
 --=====================================================================================================================
 --      Conditionally drop and recreate the test DataTable table.
 --=====================================================================================================================

 IF OBJECT_ID('dbo.DataTable') IS NOT NULL
 DROP TABLE dbo.DataTable 
 GO

 CREATE TABLE dbo.DataTable 
 (
     [1] VARCHAR(10),
     [2] VARCHAR(10),
     [3] VARCHAR(10),
     [4] VARCHAR(10),
     [5] VARCHAR(10) 
 )
 GO

 --=====================================================================================================================
 --      Conditionally drop and recreate the Splitter_DelimitedSplit8K_Returning_Concatenated_String function.
 --=====================================================================================================================
 IF OBJECT_ID('dbo.Splitter_DelimitedSplit8K_Returning_Concatenated_String', 'IF') IS NOT NULL
 DROP FUNCTION [dbo].[Splitter_DelimitedSplit8K_Returning_Concatenated_String]
 GO

 /*===== USES BINARY COLLATION TO SPEED UP THE STRING COMPARISON=======*/
 /*===== FIRST DELIMITED ITEM HANDLED SEPERATELY=======================*/
 CREATE FUNCTION [dbo].[Splitter_DelimitedSplit8K_Returning_Concatenated_String]
 --===== Define I/O parameters
 (@pString VARCHAR(8000), @pDelimiter VARCHAR(1)/*===MADE IT VARCHAR TO AVOID IMPLICIT CONVERSION WHILE COMPARING====*/
 )
 RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
 --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
      -- enough to cover VARCHAR(8000)
   WITH E1(N) AS (
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                 ),                          --10E+1 or 10 rows
        E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
        E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
   cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                      -- for both a performance gain and prevention of accidental "overruns"
                  SELECT TOP (ISNULL(DATALENGTH(@pString),0)) CONVERT(INT,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) 
                  FROM E4
                 ),
   cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                     SELECT N+1
                     FROM cteTally  
                     WHERE SUBSTRING(@pString ,N,1) COLLATE Latin1_General_BIN = @pDelimiter COLLATE Latin1_General_BIN
                 ),
   cteLen(N1,L1)AS (--==== Return start and length (for use in substring)
                     SELECT N1,
                            ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,
                                             @pString COLLATE Latin1_General_BIN,N1)
                                     ,0) - N1
                                 ,8000)
                     FROM cteStart 
                 ),
     
     --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
     cteSplitter(Item) 
             AS (
     /*====FIRST ITEM HANDLING===============*/
                     SELECT SUBSTRING(@pString , 1, ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,
                                                                                     @pString COLLATE Latin1_General_BIN)
                                                                 ,0)-1
                                                             , 8000)
                                         ) 
                     UNION ALL  
                     SELECT SUBSTRING(@pString , N1, L1)
                     FROM cteLen
                 )
     SELECT (SELECT DISTINCT '|' + [Item] [*] 
             FROM [cteSplitter]
             FOR XML PATH('')
             ) + '|' MergedConcatenatedValue

 ;
 GO

 --=====================================================================================================================
 --      Populate the test Data in [DataTable] table.
 --=====================================================================================================================
 INSERT dbo.[DataTable]
         (
          [1]
         ,[2]
         ,[3]
         ,[4]
         ,[5]
         )
         SELECT 1, 1, 2, 3, 3
         UNION ALL
         SELECT 2, 2, 3, 4, 4

 GO

 --=====================================================================================================================
 --      The actual solution.
 --=====================================================================================================================

 /*==== Concatenate the Columns values using '|' as the delimiter =====================================================*/
 ;WITH MainDataCTE AS
 (
 SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowNum ,
 ISNULL([1],'') + '|' + ISNULL([2],'') + '|' + ISNULL([3],'') + '|' + ISNULL([4],'') + '|' + ISNULL([5],'') DelimitedData
 FROM [dbo].[DataTable]
 )

 /*==== Concatenate the column names with '|' as the Delimiter using XML path ==========================================*/
 SELECT (SELECT '|' + C.[name] 
         FROM sys.[objects] O
         INNER JOIN sys.[columns] C
         ON [O].[object_id] = [C].[object_id]
         WHERE [O].[name] = 'DataTable'
         FOR XML PATH('') 
         ) + '|' [|----|],
         '-----|'[-----|],
         '-----|'[-----|],
         '-----|'[-----|],
         '-----|'[-----|]
 UNION ALL -- Concatenate the Outputs
 /*==== Using the 8k splitter, return the distinct values with '|' as the delimiter ======================================*/
 SELECT MergedConcatenatedValue [|----|],
         '-----|'[-----|],
         '-----|'[-----|],
         '-----|'[-----|],
         '-----|'[-----|]

 FROM [MainDataCTE]
 CROSS APPLY 
 [dbo].[Splitter_DelimitedSplit8K_Returning_Concatenated_String]([DelimitedData], '|') AS DSKTFIHS
 OPTION(MAXDOP 1)
more ▼

answered May 31, 2012 at 07:10 AM

avatar image

Usman Butt
13.9k 6 12 21

(comments are locked)
10|1200 characters needed characters left

1 2 3 4 5 1 2 3

more ▼

answered May 31, 2012 at 03:14 PM

avatar image

Bildoss
0 1 1 1

HI Ken,

Col1 Col2 Col3 Col4 Col5
1 2 3

In the above format the data should come.

May 31, 2012 at 03:16 PM Bildoss

Col1 and col2 --> 1 Col2 --> 2 Col3 and col4 -->3

May 31, 2012 at 03:18 PM Bildoss

@Bildoss Can you please post the desired results for these two rows

    SELECT 1, 1, 2, 3, 3
    UNION ALL
    SELECT 1, 2, 2, 3, 4
Jun 01, 2012 at 05:54 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

I am implementing apriori algorithm in sql. I am stuck with this. How to convert L2 to FIset? L2( itemid1 itemid2) i1 i2 i1 i3 i1 i5 i2 i3

FIset( setId itemid) 1 i1 1 i2 2 i1 2 i3 3 i1 3 i5 4 i2 4 i3

Please help.

more ▼

answered Nov 25, 2012 at 05:37 PM

avatar image

Atihska
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53
x41

asked: May 30, 2012 at 06:49 PM

Seen: 4324 times

Last Updated: Nov 26, 2012 at 01:54 AM

Copyright 2016 Redgate Software. Privacy Policy