Reg: Merge multiple columns into single column data

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)

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.

1 2 3 4 5 1 2 3