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 '12 at 06:49 PM in Default

Bildoss gravatar 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 '12 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 '12 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 '12 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 '12 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 '12 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 '12 at 07:10 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
1 2 3 4 5 1 2 3
more ▼

answered May 31 '12 at 03:14 PM

Bildoss gravatar 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 '12 at 03:16 PM Bildoss
Col1 and col2 --> 1 Col2 --> 2 Col3 and col4 -->3
May 31 '12 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 '12 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 '12 at 05:37 PM

Atihska gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x47
x44

asked: May 30 '12 at 06:49 PM

Seen: 3079 times

Last Updated: Nov 26 '12 at 01:54 AM