[RESOLVED] Tough SQL (like a cross table) -- SQL Server 2008R2

[GaryMazzone]

Hi guys

I using SQL Server 2008R2 and need to convert a vertical table (Key-Values pairs) into a standard looking SQL Server table.....

The data comes into the db like this

ReportDumpId Int Indentity
ReportId Int
KeyName NVARCHAR(2000)
KevyValue NVARCHAR(2000)
KeyType NVARCHAR(255)


and a sample of the data:

ReportDumpId ReportId ItemId KeyName KeyValue KeyType
756 49415 2237099 referencenumber 2237099 notype
757 49415 2237099 referencenumberusrprefix USR2237099 notype
758 49415 2237099 category Academic (professor, student, researcher) notype
759 49415 2237099 attendeerecordurl https://www.eiseverywhere.com/ereg/r...QOt757xQ%3D%3D attendee
760 49415 2237099 registered 2011-07-19 notype
761 49415 2237099 registereddateandtime 2011-07-19 12:20:56 notype
762 49415 2237099 registeredby attendee notype
763 49415 2237099 registrationstatus Confirmed notype
764 49415 2237099 approvalstatus Approved notype
765 49415 2237099 modified 2011-07-19 notype


What I ultimatlely need is something that looks like this:
ItemId referencenumber referencenumberusrprefix and so on....
2237099 2237099 USR2237099


Any one got any ideas?

I was thinking of a cross tabl but can't seem to get my head around it.

[wild_bill]

You could join the table to itself

declare @temp table(ReportDumpId int, ReportId int, ItemId int, KeyName nvarchar(2000), KeyValue nvarchar(2000), KeyType nvarchar(255))

INSERT INTO @temp(ReportDumpId, ReportId, ItemId, KeyName, KeyValue, KeyType) VALUES
(756, 49415, 2237099, 'referencenumber', '2237099', 'notype'),
(757, 49415, 2237099, 'referencenumberusrprefix', 'USR2237099', 'notype'),
(758, 49415, 2237099, 'category Academic', '(professor, student, researcher)', 'notype'),
(759, 49415, 2237099, 'attendeerecordurl', 'https://www.eiseverywhere.com/ereg/r...QOt757xQ%3D%3D', 'attendee'),
(760, 49415, 2237099, 'registered', '2011-07-19', 'notype'),
(761, 49415, 2237099, 'registereddateandtime', '2011-07-19 12:20:56', 'notype'),
(762, 49415, 2237099, 'registeredby', 'attendee', 'notype'),
(763, 49415, 2237099, 'registrationstatus', 'Confirmed', 'notype'),
(764, 49415, 2237099, 'approvalstatus', 'Approved', 'notype'),
(765, 49415, 2237099, 'modified', '2011-07-19', 'notype')


select t1.itemid, t1.KeyValue as 'referencenumber', t2.KeyValue as 'referencenumberusrprefix'
from @temp t1
inner join @temp t2 on t1.ItemId = t2.ItemId and t2.KeyName = 'referencenumberusrprefix'
where t1.KeyName = 'referencenumber'

[GaryMazzone]

The problem is that there are an unknown number of KeyValue pairs (this on in the example there are 117 pairs).

Writing 117 joins will be an issue.

also the table that holds the temp data is as show Identity number for a row, the reportId this is from, the fieldName , the fieldvalue (the KeyValue pair) and then a KeyType (I might use this or not yet)

[GaryMazzone]

OK this is nasty but it does work and converts the vertical table of Key Value pairs to a normal looking table:

SET NOCOUNT ON;
 
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tempTestTable')
    BEGIN
        DROP TABLE tempTestTable
    END 
 
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tempColumnNames')
    BEGIN
        DROP TABLE tempColumnNames
    END 
 
CREATE TABLE tempColumnNames (
    CoumnName VARCHAR(2000)
    )
 
DECLARE @ItemId INT
DECLARE @sql NVARCHAR(MAX) ,
        @sqlSelect NVARCHAR(MAX) ,
        @ColumnName AS VARCHAR(2000)
        
DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY FOR 
    SELECT TOP 1 ItemId FROM eTouchs.tempReportDump  
        ORDER BY 1
 
OPEN c1
FETCH NEXT FROM c1 INTO @ItemId
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'Create Table tempTestTable (ItemId BIGINT, '
        SET @sqlSelect = 'SELECT ItemId, '
        --print @ItemId
        DECLARE c2 CURSOR Forward_only Read_only FOR
            SELECT KeyName FROM eTouchs.tempReportDump 
                WHERE ItemId = @ItemId 
                ORDER BY KeyName 
        OPEN c2
        FETCH NEXT FROM c2 INTO @ColumnName
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @sql = @sql + @columnName + N' VARCHAR(MAX) ,'
                INSERT INTO tempColumnNames VALUES ( @ColumnName )
                SET @sqlSelect = @sqlSelect + @ColumnName + ', '
                FETCH NEXT FROM c2 INTO @ColumnName
            END 
        CLOSE c2
        DEALLOCATE c2
        SET @sql = SUBSTRING(@sql,1,LEN(@sql) -1) + N')'
        SET @sqlSelect = SUBSTRING (@sqlSelect ,1,LEN(@sqlSelect) -1 ) + N' FROM eTouchs.tempReportDump Order By ItemId'
        FETCH NEXT FROM c1 INTO @ItemId
    END     
CLOSE c1
DEALLOCATE c1
--Creates the temp table to hold results
EXECUTE sp_executesql @sql
 
--Now insert a Qnique set of itemIds 
INSERT INTO tempTestTable (ItemId) SELECT DISTINCT ItemId FROM eTouchs.tempReportDump 
 
--Now we update 
DECLARE @updateSQL NVARCHAR(MAX) ,
        @keyVal NVARCHAR(4000)
        
DECLARE c1 CURSOR FORWARD_ONLY READ_ONLY FOR 
    SELECT DISTINCT ItemId FROM eTouchs.tempReportDump 
 
OPEN c1
FETCH NEXT FROM c1 INTO @ItemId
WHILE @@FETCH_STATUS = 0
    BEGIN
        --Now we need to build up an update statement
        DECLARE c2 CURSOR FORWARD_ONLY READ_ONLY FOR 
            SELECT CoumnName FROM tempColumnNames 
        OPEN c2
        FETCH NEXT FROM c2 INTO @columnName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @keyVal = KeyValue FROM eTouchs.tempReportDump WHERE ItemId = @ItemId AND KeyName = @ColumnName 
            SET @updateSQL = 'UPDATE tempTestTable Set '+ @columnName + ' = ''' + @keyVal + ''' WHERE ItemId = ' + CONVERT(NVARCHAR(100),@itemId)       
            BEGIN TRY
                BEGIN
                    EXECUTE sp_executesql @updateSQL
                END 
            END TRY
            BEGIN CATCH
                BEGIN
                    PRINT @updateSQL
                    PRINT @@ERROR
                END
            END CATCH 
            FETCH NEXT FROM c2 INTO @columnName
        END 
        CLOSE c2
        DEALLOCATE c2   
        FETCH NEXT FROM c1 INTO @ItemId
    END
CLOSE c1
DEALLOCATE c1

[wild_bill]

I go close using XML, but there is no select * from @xml, so I got stuck. It might be useful, so I thought I would post it.

declare @temp table(ReportDumpId int, ReportId int, ItemId int, KeyName nvarchar(2000), KeyValue nvarchar(2000), KeyType nvarchar(255))

INSERT INTO @temp(ReportDumpId, ReportId, ItemId, KeyName, KeyValue, KeyType) VALUES
(756, 49415, 2237099, 'referencenumber', '2237099', 'notype'),
(757, 49415, 2237099, 'referencenumberusrprefix', 'USR2237099', 'notype'),
(758, 49415, 2237099, 'category', 'Academic (professor, student, researcher)', 'notype'),
(759, 49415, 2237099, 'attendeerecordurl', 'https://www.eiseverywhere.com/ereg/r...QOt757xQ%3D%3D', 'attendee'),
(760, 49415, 2237099, 'registered', '2011-07-19', 'notype'),
(761, 49415, 2237099, 'registereddateandtime', '2011-07-19 12:20:56', 'notype'),
(762, 49415, 2237099, 'registeredby', 'attendee', 'notype'),
(763, 49415, 2237099, 'registrationstatus', 'Confirmed', 'notype'),
(764, 49415, 2237099, 'approvalstatus', 'Approved', 'notype'),
(765, 49415, 2237099, 'modified', '2011-07-19', 'notype')

declare @work varchar(max)
	--create single row of data
	set @work = 
	(select Tag, Parent, [ROOT!1!ReportId],[ROOT!1!ItemId],[CHILD!2!KeyName],[CHILD!2!KeyValue] From 
(select distinct 1 AS Tag
		,NULL as Parent
		,ReportId as [ROOT!1!ReportId]
		,ItemId as [ROOT!1!ItemId]
		,NULL as [CHILD!2!KeyName]
		,NULL as [CHILD!2!KeyValue]
	from @temp
	UNION ALL
	select 2 as Tag
		,1 as Parent
		,ReportId 
		,ItemId
		,KeyName
		,KeyValue
	FROM @temp) as t
	FOR XML EXPLICIT)
	
	--tweak xml
	set @work = REPLACE(@work,'CHILD KeyName="','')
	set @work = REPLACE(@work,'" KeyValue',' KeyValue')
	
	select cast(@work as xml)

[techgnome]

wait.... hold on.... hang on... I think I might have something here...
Take a look at the stuff function

I discovered it while looking for a solution to pivot tables dynamically... an example of how it works:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

from here: http://www.simple-talk.com/community.../14/37265.aspx

I just reread the posting... you need to pivot... take a look at the link I jsut posted... it might be what you need.. a dynamic pivot table... the down side is that it means dynamic SQL... so you'll want to be sure of the data you're toying with. It's not an ideal solution, but it does work... I was using it up to about a week ago when I realized I was over complicating something that should have been simple.

-tg

[GaryMazzone]

Here is what I came out with in the end:

DECLARE @cols NVARCHAR(MAX) , 
        @query NVARCHAR(MAX) ,
        @cols2 NVARCHAR(MAX) ,
        @query2 NVARCHAR(MAX)
 
SELECT  
    @cols = ISNULL(@cols + ', ', '') + QUOTENAME(tc.keyname) 
FROM 
    eTouchs.tempReportDump tc 
WHERE tc.KeyType != 'question'
GROUP BY KeyName
ORDER BY KeyName
 
 
SELECT  
    @cols2 = ISNULL(@cols2 + ', ', '') + QUOTENAME(tc.keyname) 
FROM 
    eTouchs.tempReportDump tc 
WHERE tc.KeyType = 'question'
GROUP BY KeyName
ORDER BY KeyName
 
 
SET @query = N'SELECT  ItemId, ' + @cols + '
FROM (
    select 
       ItemId, 
       KeyName, 
       KeyValue 
    from eTouchs.tempReportDump) srcData  
    PIVOT (
       max(KeyValue)
       FOR KeyName IN
       ( ' + @cols + ' )
       ) AS pvt
ORDER BY  ItemId';
 
 
SET @query2 = N'SELECT  ItemId, ' + @cols2 + '
FROM (
    select 
       ItemId, 
       KeyName, 
       KeyValue 
    from eTouchs.tempReportDump) srcData  
    PIVOT (
       max(KeyValue)
       FOR KeyName IN
       ( ' + @cols2 + ' )
       ) AS pvt
ORDER BY ItemId';
 
--This is non-question fields
EXECUTE sp_executesql @query
--This is question fields
EXECUTE sp_executesql @query2

Yes the field names can change at any time. The number of fields can change at any time. Isn't standardazation great.

[techgnome]

Yup... looks very similar to the SQL I was working with... I had the added bonus that within my pivot, I was summing (budgets) ... so I had to have two cols as well... one for the cols themselves .... and one for the group by ... that was a joy....

-tg