sravan.here (4/19/2013)
All the similar rows should go into one table. In my example, row 1 and 3 will be in table and 2 will be another table. but my metatable will have thousands of rows and possibly they have 10 or 20 tables that will have to be created.
This is straightforward to do, coding against the simple sample dataset:
SET NOCOUNT ON
------------------------------------------------------------------
-- get the list of tables and their column list
------------------------------------------------------------------
IF object_id('tempdb..#TableList') IS NOT NULL
DROP TABLE #TableList
SELECT DISTINCT
TableCount = MAX(TableID) OVER(PARTITION BY 1),
TableID,
TableName = 'Staging.dbo.Table' + RIGHT('00'+CAST(TableID AS VARCHAR(3)),3),
ColumnNumber,
ColumnName
INTO #TableList
FROM (
SELECT
TableID = DENSE_RANK() OVER(ORDER BY yt.[Name]),
ColumnNumber = x.ItemNumber,
ColumnName = x.Item
FROM yt
CROSS APPLY dbo.DelimitedSplit8K(yt.[Name],',') x
) d
ORDER BY TableID, ColumnNumber
------------------------------------------------------------------
-- shape the data for convenience
------------------------------------------------------------------
IF object_id('tempdb..#ProcessedData') IS NOT NULL
DROP TABLE #ProcessedData
;WITH ProcessedData AS (
SELECT
TableID = DENSE_RANK() OVER(ORDER BY [Name]),
RowID = ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY ID),
[Name],
[Text]
FROM yt
)
SELECT
TableID,
RowID,
x.ItemNumber,
ColumnName = x.Item,
ColumnValue = y.Item
INTO #ProcessedData
FROM ProcessedData yt
CROSS APPLY dbo.DelimitedSplit8K([Name],',') x
CROSS APPLY dbo.DelimitedSplit8K([Text],',') y
WHERE y.ItemNumber = x.ItemNumber
ORDER BY TableID, RowID
------------------------------------------------------------------
-- Cycle through the list of tables, create or truncate
-- Then load with data
------------------------------------------------------------------
-- set up some variables
DECLARE @TableID INT, @ColumnList VARCHAR(1000), @TableName VARCHAR(100), @Script VARCHAR(2000)
SET @TableID = 1
WHILE @TableID > 0
BEGIN
-- create or truncate the table
SELECT @TableName = TableName
FROM #TableList
WHERE TableID = @TableID
IF @@ROWCOUNT = 0 BREAK
SET @ColumnList = NULL
SELECT @ColumnList = ISNULL(@ColumnList + ', ',' ') + ColumnName + ' VARCHAR(100)'
FROM #TableList
WHERE TableID = @TableID
ORDER BY ColumnNumber
SET @Script =
'IF object_id(''' + @TableName + ''') IS NULL' + CHAR(10) +
'CREATE TABLE ' + @TableName + ' (' + @ColumnList + ') ' + CHAR(10) +
'ELSE ' + CHAR(10) +
'TRUNCATE TABLE ' + @TableName
PRINT ''
PRINT @Script
EXEC(@Script)
-- insert data into table @TableName
SET @ColumnList = NULL
SELECT @ColumnList = ISNULL(@ColumnList + ', ', ' ') +
'[' + ColumnName + '] = MAX(CASE WHEN ItemNumber = ' + CAST(ColumnNumber AS VARCHAR(3)) +
' THEN ColumnValue END)' + CHAR(10)
FROM #TableList
WHERE TableID = @TableID
ORDER BY ColumnNumber
SET @Script =
'INSERT INTO ' + @TableName + CHAR(10) +
'SELECT ' + CHAR(10) + @ColumnList +
'FROM #ProcessedData ' + CHAR(10) +
'WHERE TableID = ' + CAST(@TableID AS VARCHAR(3)) + CHAR(10) +
'GROUP BY RowID'
PRINT ''
PRINT @Script
EXEC(@Script)
PRINT ''
PRINT '-----------------------------------------------------------------'
SET @TableID = @TableID + 1
END
SELECT * FROM Staging.dbo.Table001
SELECT * FROM Staging.dbo.Table002
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden