StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can use the <a href="http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx">PIVOT</a> function of SQL Server to get the result:</p> <pre><code>select id, feature, FeatureValue_1, FeatureValue_2, FeatureValue_3 from ( SELECT tartikel.cArtNr AS ID, tMerkmal.cName AS Feature, tMerkmalWertSprache.cWert AS FeatureValue, 'FeatureValue_'+cast(row_number() over(partition by tartikel.cArtNr, tMerkmal.cName order by tMerkmal.cName) as varchar(10)) seq FROM tartikel INNER JOIN tArtikelMerkmal ON tartikel.kArtikel = tArtikelMerkmal.kArtikel INNER JOIN tMerkmal ON tArtikelMerkmal.kMerkmal = tMerkmal.kMerkmal INNER JOIN tMerkmalWertSprache ON tArtikelMerkmal.kMerkmalWert = tMerkmalWertSprache.kMerkmalWert WHERE tMerkmalWertSprache.kSprache = '1' ) d pivot ( max(FeatureValue) for seq in (FeatureValue_1, FeatureValue_2, FeatureValue_3) ) piv; </code></pre> <p>If you had an unknown number of FeatureValues for each Id, then you could use dynamic SQL:</p> <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME('FeatureValue_'+cast(row_number() over(partition by tartikel.cArtNr, tMerkmal.cName order by tMerkmal.cName) as varchar(10))) FROM tartikel INNER JOIN tArtikelMerkmal ON tartikel.kArtikel = tArtikelMerkmal.kArtikel INNER JOIN tMerkmal ON tArtikelMerkmal.kMerkmal = tMerkmal.kMerkmal INNER JOIN tMerkmalWertSprache ON tArtikelMerkmal.kMerkmalWert = tMerkmalWertSprache.kMerkmalWert WHERE tMerkmalWertSprache.kSprache = '1' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT id, feature,' + @cols + ' from ( SELECT tartikel.cArtNr AS ID, tMerkmal.cName AS Feature, tMerkmalWertSprache.cWert AS FeatureValue, ''FeatureValue_''+cast(row_number() over(partition by tartikel.cArtNr, tMerkmal.cName order by tMerkmal.cName) as varchar(10)) seq FROM tartikel INNER JOIN tArtikelMerkmal ON tartikel.kArtikel = tArtikelMerkmal.kArtikel INNER JOIN tMerkmal ON tArtikelMerkmal.kMerkmal = tMerkmal.kMerkmal INNER JOIN tMerkmalWertSprache ON tArtikelMerkmal.kMerkmalWert = tMerkmalWertSprache.kMerkmalWert WHERE tMerkmalWertSprache.kSprache = ''1'' ) x pivot ( max(FeatureValue) for seq in (' + @cols + ') ) p ' execute(@query) </code></pre>

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