{"id":1614,"date":"2013-04-01T00:00:00","date_gmt":"2013-03-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/tsql-pivot-rotations-using-only-replace\/"},"modified":"2021-09-29T16:21:45","modified_gmt":"2021-09-29T16:21:45","slug":"tsql-pivot-rotations-using-only-replace","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/tsql-pivot-rotations-using-only-replace\/","title":{"rendered":"TSQL Pivot Rotations using only REPLACE"},"content":{"rendered":"
\n

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.<\/p>\n

\/*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: *\/\n--first let's get some data.\n \nCREATE TABLE #WelshWordsForNumbers\n(number INT, word VARCHAR(30))\n \nINSERT INTO #WelshWordsForNumbers (Number, Word)\n          SELECT 1, 'un'\nunion all SELECT 2, 'dau' \nunion all SELECT 3, 'tri'\nunion all SELECT 4, 'pedwar'\nunion all SELECT 5, 'pump'\nunion all SELECT 6, 'chwech'\nunion all SELECT 7, 'saith'\nunion all SELECT 8, 'wyth'\nunion all SELECT 9, 'naw'\nunion all SELECT 10,'deg' \n \nDECLARE @string VARCHAR(MAX)\nSELECT @String=REPLACE(COALESCE(@String,'%'),'%', word+',%') \n  FROM #WelshWordsForNumbers ORDER BY Number\nSELECT REPLACE(@String,',%','')\n\/* with the result\n------------------------------------------------\nun,dau,tri,pedwar,pump,chwech,saith,wyth,naw,deg\n \n(1 row(s) affected)\n \nof course, for large tables, this is much more efficient, though it is more difficult to remember the syntax when coding in a hurry *\/\nSELECT  STUFF(\n  (SELECT ','+word FROM #WelshWordsForNumbers \n  FOR XML PATH(''), TYPE).value('.', 'varchar(max)')\n  ,1,1,'')  \n\/* with the same result\n------------------------------------------------\nun,dau,tri,pedwar,pump,chwech,saith,wyth,naw,deg\n \nThe problem with performance for a large amount of data is that SQL Server and .NET has a problem with rapid repeated manipulation of strings. \n \n\nThere 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 \n \nImagine we have a result where two columns need to be listed in the same order to get the result we want. \nTea, Coffee,Cocoa shipped from China, Brazil, America respectively.\nThis 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*\/ \n \n \nDeclare @Respectively Varchar(8000)\nSelect @Respectively=\n         Replace(Replace(\n            coalesce(@Respectively, 'I''m told that <t> is shipped from <p> respectively'),\n            '<t>',beverage+', <t>'),\n         '<p>',producer+', <p>')\nfrom \n  (Select 'Tea' as beverage, 'China' as producer\n   union all Select 'Coffee', 'Brazil'\n   union all Select 'Cocoa', 'America') data\nSelect Replace(replace(@Respectively,', <p>',''),', <t>','')\n \n\/*\n---------------------------------------------------------------------------------------------------\nI'm told that Tea, Coffee, Cocoa is shipped from China, Brazil, America respectively\n \n(1 row(s) affected)\n \n \nHmm. 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. \n \nBut 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  *\/\n \n\/* lets make things easy by creating a temporary table *\/\nCREATE TABLE #BritishWordsForNumbers\n(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))\n\n\/* and insert our experimental data into it. These are taken from the Wikipedia entry *\/\nINSERT into #BritishWordsForNumbers(\nnumber,Bowland,Rathmell,Nidderdale,Swaledale,Teesdale,Derbyshire,Weardale,\nTong,[Kirkby Lonsdale],Wensleydale,[Derbyshire Dales],Lincolnshire,\n[Southern England],Wilts)\nSELECT 1,'Yain','Aen','Yain','Yan','Yan','Yain','Yan','Yan','Yaan','Yain','Yan','Yan','Yahn','Hant'\nunion all SELECT 2,'Tain','Taen','Tain','Tan','Tean','Tain','Teyan','Tan','Tyaan','Tain','Tan','tan','Tayn','Tant'\nunion all SELECT 3,'Eddera','Tethera','Eddero','Tether','Tether','Eddero','Tethera','Tether','Taed''ere','Eddero','Tethera','Tethera','Tether','Tothery'\nunion all SELECT 4,'Peddera','Fethera','Peddero','Mether','Mether','Pederro','Methera','Mether','Mead''ere','Peddero','Methera','Pethera','Mether','Forthery'\nunion all SELECT 5,'Pit','Phubs','Pitts','Pip','Pip','Pitts','Tic','Pick','Mimp','Pitts','Pip','Pimp','Mumph','Fant'\nunion all SELECT 6,'Tayter','Aayther','Tayter','Azer','Lezar','Tayter','Yan-a-tic','Sesan','Haites','Tayter','Sethera','Sethera','Hither','Sahny'\nunion all SELECT 7,'Layter','Layather','Layter','Sezar','Azar','Later','Teyan-a-tic','Asel','Saites','Later','Lethera','Lethera','Lither','Dahny'\nunion all SELECT 8,'Overa','Quoather','Overo','Akker','Catrah','Overro','Tethera-tic','Catel','Haoves','Overro','Hovera','Hovera','Auver','Downy'\nunion all SELECT 9,'Covera','Quaather','Covero','Conter','Borna','Coverro','Methera-tic','Oiner','Daoves','Coverro','Dovera','Covera','Dauver','Dominy'\nunion all SELECT 10,'Dix','Dugs','Dix','Dick','Dick','Dix','Bub','Dick','Dik','Disc','Dick','Dik','Dic','Dik'\n \n\/* right. There you are, but it isn't exactly relational is it?  We can put that right straight away. *\/\n \nCREATE TABLE #RelationalFormat (Number int not null, Location Varchar(30) not null, [Celtic Word] Varchar(30)not null)\n \nInsert into #RelationalFormat\nSelect number,'Bowland' as location,Bowland as 'Celtic Word' from #BritishWordsForNumbers union all\nSelect number,'Rathmell',Rathmell from #BritishWordsForNumbers union all\nSelect number,'Nidderdale',Nidderdale from #BritishWordsForNumbers union all\nSelect number,'Swaledale',Swaledale from #BritishWordsForNumbers union all\nSelect number,'Teesdale',Teesdale from #BritishWordsForNumbers union all\nSelect number,'Derbyshire',Derbyshire from #BritishWordsForNumbers union all\nSelect number,'Weardale',Weardale from #BritishWordsForNumbers union all\nSelect number,'Tong',Tong from #BritishWordsForNumbers union all\nSelect number,'Kirkby Lonsdale',[Kirkby Lonsdale] from #BritishWordsForNumbers union all\nSelect number,'Wensleydale',Wensleydale from #BritishWordsForNumbers union all\nSelect number,'Derbyshire Dales',[Derbyshire Dales] from #BritishWordsForNumbers union all\nSelect number,'Lincolnshire',Lincolnshire from #BritishWordsForNumbers union all\nSelect number,'Southern England',[Southern England] from #BritishWordsForNumbers union all\nSelect number,'Wilts',Wilts from #BritishWordsForNumbers\n \n\n\/* now, we dynamically create an XML Fragment that represents the data *\/\nDECLARE @string VARCHAR(8000),@XMLlist xml\nSELECT @string='\n<array>\n<r><c>Bowland<\/c>%Bowland\n<r><c>Rathmell<\/c>%Rathmell\n<r><c>Nidderdale<\/c>%Nidderdale\n<r><c>Swaledale<\/c>%Swaledale\n<r><c>Teesdale<\/c>%Teesdale\n<r><c>Derbyshire<\/c>%Derbyshire\n<r><c>Weardale<\/c>%Weardale\n<r><c>Tong<\/c>%Tong\n<r><c>Kirkby Lonsdale<\/c>%Kirkby Lonsdale\n<r><c>Wensleydale<\/c>%Wensleydale\n<r><c>Derbyshire Dales<\/c>%Derbyshire Dales\n<r><c>Lincolnshire<\/c>%Lincolnshire\n<r><c>Southern England<\/c>%Southern England\n<r><c>Wilts<\/c>%Wilts\n<\/array>'\n \nSelect @String=Replace (@String,'%'+location,'<c>'+[Celtic Word]+'<\/c>%'+location) from #relationalFormat order by number\nSelect @String=Replace (@String,'%'+location,'<\/r>') from #relationalFormat\nSelect @String\n\nSelect @XMLList=@String \/* and convert to XML*\/\n \n\/* and all we need to do now is to shred the XML into a SQL Result *\/\nSELECT\nRows.n.value('(c)[1]', 'varchar(20)') AS [Location],\nRows.n.value('(c)[2]', 'varchar(20)') AS [One],\nRows.n.value('(c)[3]', 'varchar(20)') AS [Two],\nRows.n.value('(c)[4]', 'varchar(20)') AS [Three],\nRows.n.value('(c)[5]', 'varchar(20)') AS [Four],\nRows.n.value('(c)[6]', 'varchar(20)') AS [Five],\nRows.n.value('(c)[7]', 'varchar(20)') AS [Six],\nRows.n.value('(c)[8]', 'varchar(20)') AS [Seven],\nRows.n.value('(c)[9]', 'varchar(20)') AS [Eight],\nRows.n.value('(c)[10]', 'varchar(20)') AS [Nine],\nRows.n.value('(c)[11]', 'varchar(20)')AS [Ten]\nFROM @xmlList.nodes('\/\/r') Rows(n)\n \n \n \n<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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 PIVOY, here is a way to do it using only REPLACE.…<\/p>\n","protected":false},"author":221862,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4252,4190],"coauthors":[],"class_list":["post-1614","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-t-sql-programming","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221862"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1614"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1614\/revisions"}],"predecessor-version":[{"id":40891,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1614\/revisions\/40891"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1614"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}