clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921333 fiddles created (11969 in the last week).

CREATE TABLE #XMLTempTable( ID INT NOT NULL IDENTITY PRIMARY KEY ,DataName VARCHAR(20) NOT NULL ,DataValue VARCHAR(20) NOT NULL ); INSERT INTO #XMLTempTable(DataName,DataValue) VALUES ('blahblah','blah-1'); INSERT INTO #XMLTempTable(DataName,DataValue) VALUES ('userid','user-1'); INSERT INTO #XMLTempTable(DataName,DataValue) VALUES ('value','value-1'); INSERT INTO #XMLTempTable(DataName,DataValue) VALUES ('blahblah','blah-2'); INSERT INTO #XMLTempTable(DataName,DataValue) VALUES ('userid','user-2'); INSERT INTO #XMLTempTable(DataName,DataValue) VALUES ('value','value-3');
6 rows affected
 hidden batch(es)


SELECT id , blahblah = CASE WHEN DataName = 'blahblah' THEN datavalue END , userid = CASE WHEN DataName = 'userid' THEN DataValue END , value = CASE WHEN DataName = 'value' THEN DataValue END FROM #XMLTempTable WHERE DataName in ('blahblah', 'userid', 'value')
id blahblah userid value
1 blah-1
2 user-1
3 value-1
4 blah-2
5 user-2
6 value-3
 hidden batch(es)


SELECT (id + 2) / 3 as rowno , max(CASE WHEN DataName = 'blahblah' THEN datavalue END) as blahblah , max(CASE WHEN DataName = 'userid' THEN DataValue END) as userid , max(CASE WHEN DataName = 'value' THEN DataValue END) as datavalue FROM #XMLTempTable WHERE DataName in ('blahblah', 'userid', 'value') GROUP BY (id + 2) / 3
rowno blahblah userid datavalue
1 blah-1 user-1 value-1
2 blah-2 user-2 value-3
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)