Pivoting SQL Server tables is always awkward, even with the PIVOT and UNPIVOT operators. If you want to get the job done without GROUP BY or PIVOT, here is a way to do it using only REPLACE.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
/*There is a curiosity of any REPLACE function that allows you to build lists. There is nothing particularly exciting about the fact since there are easier, or quicker, ways of doing so, which is why it remains a curiosity. However, in TSQL, it allows some simple techniques that are, otherwise, irritatingly complicated to do. I'll start by illustrating the simplest form: */ --first let's get some data. CREATE TABLE #WelshWordsForNumbers (number INT, word VARCHAR(30)) INSERT INTO #WelshWordsForNumbers (Number, Word) SELECT 1, 'un' union all SELECT 2, 'dau' union all SELECT 3, 'tri' union all SELECT 4, 'pedwar' union all SELECT 5, 'pump' union all SELECT 6, 'chwech' union all SELECT 7, 'saith' union all SELECT 8, 'wyth' union all SELECT 9, 'naw' union all SELECT 10,'deg' DECLARE @string VARCHAR(MAX) SELECT @String=REPLACE(COALESCE(@String,'%'),'%', word+',%') FROM #WelshWordsForNumbers ORDER BY Number SELECT REPLACE(@String,',%','') /* with the result ------------------------------------------------ un,dau,tri,pedwar,pump,chwech,saith,wyth,naw,deg (1 row(s) affected) of course, for large tables, this is much more efficient, though it is more difficult to remember the syntax when coding in a hurry */ SELECT STUFF( (SELECT ','+word FROM #WelshWordsForNumbers FOR XML PATH(''), TYPE).value('.', 'varchar(max)') ,1,1,'') /* with the same result ------------------------------------------------ un,dau,tri,pedwar,pump,chwech,saith,wyth,naw,deg The problem with performance for a large amount of data is that SQL Server and .NET has a problem with rapid repeated manipulation of strings. There is a SQL problem that one occasionally comes across where a single result needs two lists I call it the 'Respectively' problem. Let's take it to its simplest form just so you can see what the problem is, and how it works Imagine we have a result where two columns need to be listed in the same order to get the result we want. Tea, Coffee,Cocoa shipped from China, Brazil, America respectively. This will be more common in reports, and may relate to the top five sources of revenue in each month along with the amounts. However, lets start with the simplest example. The problem is that you want the order of the two lists to match so that the value matches the label*/ Declare @Respectively Varchar(8000) Select @Respectively= Replace(Replace( coalesce(@Respectively, 'I''m told that <t> is shipped from <p> respectively'), '<t>',beverage+', <t>'), '<p>',producer+', <p>') from (Select 'Tea' as beverage, 'China' as producer union all Select 'Coffee', 'Brazil' union all Select 'Cocoa', 'America') data Select Replace(replace(@Respectively,', <p>',''),', <t>','') /* --------------------------------------------------------------------------------------------------- I'm told that Tea, Coffee, Cocoa is shipped from China, Brazil, America respectively (1 row(s) affected) Hmm. a bit academic, I'll agree. What we have here is a simple way to ensure that the sequence is the same in the two lists. I first came across this when working out a way of including multi-column foreign keys in table build scripts. But hang on. We have a way of being able to create markup. This allows us to do a pivot easily. Imagine we have a denormalised 'table' of data from some source. It's easy if you try. No, we don't have to imagine because we'll actually do it. Just for the sheer hell of it, we'll take the dialect names for the numbers between one and ten collected from various parts of Britain, using the old dialect words for counting sheep, showing how the celtic (Brythonic) language survived. Each column is from a different location. We have a denormalised table */ /* lets make things easy by creating a temporary table */ CREATE TABLE #BritishWordsForNumbers (number INT, Bowland VARCHAR(30), Rathmell VARCHAR(30), Nidderdale VARCHAR(30), Swaledale VARCHAR(30), Teesdale VARCHAR(30), Derbyshire VARCHAR(30), Weardale VARCHAR(30), Tong VARCHAR(30), [Kirkby Lonsdale] VARCHAR(30), Wensleydale VARCHAR(30),[Derbyshire Dales] VARCHAR(30), Lincolnshire VARCHAR(30),[Southern England] VARCHAR(30),Wilts VARCHAR(30)) /* and insert our experimental data into it. These are taken from the Wikipedia entry */ INSERT into #BritishWordsForNumbers( number,Bowland,Rathmell,Nidderdale,Swaledale,Teesdale,Derbyshire,Weardale, Tong,[Kirkby Lonsdale],Wensleydale,[Derbyshire Dales],Lincolnshire, [Southern England],Wilts) SELECT 1,'Yain','Aen','Yain','Yan','Yan','Yain','Yan','Yan','Yaan','Yain','Yan','Yan','Yahn','Hant' union all SELECT 2,'Tain','Taen','Tain','Tan','Tean','Tain','Teyan','Tan','Tyaan','Tain','Tan','tan','Tayn','Tant' union all SELECT 3,'Eddera','Tethera','Eddero','Tether','Tether','Eddero','Tethera','Tether','Taed''ere','Eddero','Tethera','Tethera','Tether','Tothery' union all SELECT 4,'Peddera','Fethera','Peddero','Mether','Mether','Pederro','Methera','Mether','Mead''ere','Peddero','Methera','Pethera','Mether','Forthery' union all SELECT 5,'Pit','Phubs','Pitts','Pip','Pip','Pitts','Tic','Pick','Mimp','Pitts','Pip','Pimp','Mumph','Fant' union all SELECT 6,'Tayter','Aayther','Tayter','Azer','Lezar','Tayter','Yan-a-tic','Sesan','Haites','Tayter','Sethera','Sethera','Hither','Sahny' union all SELECT 7,'Layter','Layather','Layter','Sezar','Azar','Later','Teyan-a-tic','Asel','Saites','Later','Lethera','Lethera','Lither','Dahny' union all SELECT 8,'Overa','Quoather','Overo','Akker','Catrah','Overro','Tethera-tic','Catel','Haoves','Overro','Hovera','Hovera','Auver','Downy' union all SELECT 9,'Covera','Quaather','Covero','Conter','Borna','Coverro','Methera-tic','Oiner','Daoves','Coverro','Dovera','Covera','Dauver','Dominy' union all SELECT 10,'Dix','Dugs','Dix','Dick','Dick','Dix','Bub','Dick','Dik','Disc','Dick','Dik','Dic','Dik' /* right. There you are, but it isn't exactly relational is it? We can put that right straight away. */ CREATE TABLE #RelationalFormat (Number int not null, Location Varchar(30) not null, [Celtic Word] Varchar(30)not null) Insert into #RelationalFormat Select number,'Bowland' as location,Bowland as 'Celtic Word' from #BritishWordsForNumbers union all Select number,'Rathmell',Rathmell from #BritishWordsForNumbers union all Select number,'Nidderdale',Nidderdale from #BritishWordsForNumbers union all Select number,'Swaledale',Swaledale from #BritishWordsForNumbers union all Select number,'Teesdale',Teesdale from #BritishWordsForNumbers union all Select number,'Derbyshire',Derbyshire from #BritishWordsForNumbers union all Select number,'Weardale',Weardale from #BritishWordsForNumbers union all Select number,'Tong',Tong from #BritishWordsForNumbers union all Select number,'Kirkby Lonsdale',[Kirkby Lonsdale] from #BritishWordsForNumbers union all Select number,'Wensleydale',Wensleydale from #BritishWordsForNumbers union all Select number,'Derbyshire Dales',[Derbyshire Dales] from #BritishWordsForNumbers union all Select number,'Lincolnshire',Lincolnshire from #BritishWordsForNumbers union all Select number,'Southern England',[Southern England] from #BritishWordsForNumbers union all Select number,'Wilts',Wilts from #BritishWordsForNumbers /* now, we dynamically create an XML Fragment that represents the data */ DECLARE @string VARCHAR(8000),@XMLlist xml SELECT @string=' <array> <r><c>Bowland</c>%Bowland <r><c>Rathmell</c>%Rathmell <r><c>Nidderdale</c>%Nidderdale <r><c>Swaledale</c>%Swaledale <r><c>Teesdale</c>%Teesdale <r><c>Derbyshire</c>%Derbyshire <r><c>Weardale</c>%Weardale <r><c>Tong</c>%Tong <r><c>Kirkby Lonsdale</c>%Kirkby Lonsdale <r><c>Wensleydale</c>%Wensleydale <r><c>Derbyshire Dales</c>%Derbyshire Dales <r><c>Lincolnshire</c>%Lincolnshire <r><c>Southern England</c>%Southern England <r><c>Wilts</c>%Wilts </array>' Select @String=Replace (@String,'%'+location,'<c>'+[Celtic Word]+'</c>%'+location) from #relationalFormat order by number Select @String=Replace (@String,'%'+location,'</r>') from #relationalFormat Select @String Select @XMLList=@String /* and convert to XML*/ /* and all we need to do now is to shred the XML into a SQL Result */ SELECT Rows.n.value('(c)[1]', 'varchar(20)') AS [Location], Rows.n.value('(c)[2]', 'varchar(20)') AS [One], Rows.n.value('(c)[3]', 'varchar(20)') AS [Two], Rows.n.value('(c)[4]', 'varchar(20)') AS [Three], Rows.n.value('(c)[5]', 'varchar(20)') AS [Four], Rows.n.value('(c)[6]', 'varchar(20)') AS [Five], Rows.n.value('(c)[7]', 'varchar(20)') AS [Six], Rows.n.value('(c)[8]', 'varchar(20)') AS [Seven], Rows.n.value('(c)[9]', 'varchar(20)') AS [Eight], Rows.n.value('(c)[10]', 'varchar(20)') AS [Nine], Rows.n.value('(c)[11]', 'varchar(20)')AS [Ten] FROM @xmlList.nodes('//r') Rows(n) |
Load comments