StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Removing a filter from an unpivot table
text
Body
copied!<p>I have this unpivot table and I wish to remove the filter that is applied to it. </p> <pre><code>DECLARE @colsPivot AS NVARCHAR(MAX), @colsUnpivot as NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(year(EcoDate)) from PhdRpt.RptCaseEco FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('PhdRpt.RptCaseEco') and C.name Like 'Net%' for xml path('')), 1, 1, '') set @query = 'select * from ( select reportruncaseid, year(Ecodate) as EcoYear, val, col from phdrpt.rptcaseeco unpivot ( val for col in ('+ @colsUnpivot +') ) u ) x1 pivot ( max(val) for ecoyear in ('+ @colspivot +') ) p ORDER BY reportruncaseid' exec(@query) </code></pre> <p>This table worked before because all the columns had a prefix of "Net" but now there are other columns that are being filtered out because they do not begin with "Net". I tried to remove --- and C.name Like 'Net%' --- but I keep getting these errors:</p> <p><code>Msg 8167, Level 16, State 1, Line 10 The type of column "EcoDate" conflicts with the type of other columns specified in the UNPIVOT list.</code> <br><code>Msg 207, Level 16, State 1, Line 4 Invalid column name 'reportruncaseid'.</code> <br><code>Msg 207, Level 16, State 1, Line 4 Invalid column name 'Ecodate'.</code></p> <p>Here is what the table looks like<br> <img src="https://i.stack.imgur.com/LufV1.png" alt="Table"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload