Make dynamic coloumns in sql server 2014 when duplicated values appear

Question

I'm running the following sql:

SELECT DISTINCT 
    Anvendelseskoder.[Usage Code] AS [Byggeanvendelseskode],
    Anvendelseskoder.[Usage Code Value] AS [Byggeanvendelse],
    HeleDanmark_DAWA.KVHx
FROM Anvendelseskoder 
    RIGHT JOIN HeleDanmark_DAWA 
        ON Anvendelseskoder.KVHx = HeleDanmark_DAWA.KVHx
WHERE HeleDanmark_DAWA.postnr=6720 AND Anvendelseskoder.[Usage Code]>0

This gives me the following output: (new user, cant post pictures, sorry!)

enter image description here

And I want something more like this:

enter image description here

I've been checking this question about Using PIVOT and JOIN together, but I can't get it to work.. Can anyone help me with this?

Update: The code I tried to make work, but can't make it do what I want: enter image description here

WITH Acode 
    AS (SELECT 
        HeleDanmark_DAWA.KVHx,
        Anvendelseskoder.[Usage Code],
        Anvendelseskoder.[Usage Code Value]
    FROM Anvendelseskoder
        RIGHT JOIN HeleDanmark_DAWA
            ON HeleDanmark_DAWA.KVHx = Anvendelseskoder.KVHx)
SELECT *
FROM Acode PIVOT( COUNT([Usage Code]) FOR [Usage Code Value] IN (
    [Stuehus til landbrugsejendom],
    [Fritliggende enfamilieshus (parcelhus).],
    [Række-, kæde- eller dobbelthus (lodret adskillelse mellem enhederne).],
    [Etageboligbebyggelse (flerfamiliehus, herunder tofamiliehus (vandret adskillelse mellem enhederne)).],
    [Kollegium],
    [Døgninstitution (plejehjem, alderdomshjem, børne- eller ungdomshjem).],
    [Anden enhed til helårsbeboelse],
    [Erhvervsmæssig produktion vedrørende landbrug, skovbrug, gartneri, råstofudvinding og lign.],
    [Erhvervsmæssig produktion vedrørende industri, håndværk m.v. (fabrik, værksted o. lign.)],
    [El-, gas-, vand- eller varmeværk, forbrændingsanstalt o. lign.],
    [Anden enhed til produktion og lager i forbindelse med landbrug, industri o. lign.],
    [Transport- og garageanlæg (fragtmandshal, lufthavnsbygning,banegårdsbygning o. lign.)],
    [Engroshandel og lager.],
    [Detailhandel m.v.],
    [Pengeinstitut, forsikringsvirksomhed m.v.],
    [Kontor og liberale erhverv bortset fra offentlig administration],
    [Offentlig administration.],
    [Hotel, restauration, vaskeri, frisør og anden servicevirksomhed.],
    [Anden enhed til handel, transport etc.],
    [Biograf, teater, erhvervsmæssig udstilling m.v.],
    [Bibliotek, museum, kirke o. lign.],
    [Undervisning og forskning (skole, gymnasium, forskningslaboratorium).],
    [Hospital, fødeklinik o. lign.],
    [Daginstitution.],
    [Anden institution, herunder kaserne, fængsel m.v.],
    [Sommerhus.],
    [Enhed til ferieformål m.v. bortset fra sommerhus (feriekoloni vandrehjem o. lign.)],
    [Enhed i forbindelse med idrætsudøvelse (klubhus, idrætshal, svømmehal o. lign.).],
    [Kolonihavehus.],
    [Anden enhed til fritidsformål.],
    [Ikke tidligere vurderet erhvervsenhed i nybyggeri],
    [Garage],
    [Carport],
    [Udhus]) ) AS pvt

Update code17july (Which I cant get to work):

WITH a AS (
SELECT DISTINCT 
  Anvendelseskoder.[Usage Code] AS [Byggeanvendelseskode],
  Anvendelseskoder.[Usage Code Value] AS [Byggeanvendelse],
  HeleDanmark_DAWA.KVHx
FROM Anvendelseskoder 
RIGHT JOIN HeleDanmark_DAWA 
  ON Anvendelseskoder.KVHx = HeleDanmark_DAWA.KVHx
WHERE HeleDanmark_DAWA.postnr=6720 AND Anvendelseskoder.[Usage Code]>0), 

aa AS (SELECT a.*, row_number() over(partition by KVHx order by Byggeanvendelseskode) Nr
FROM a)

SELECT KVHx, Byggeanvendelseskode AS Kode1, Byggeanvendelse AS Anvend1,
    (SELECT Byggeanvendelseskode FROM aa AS s2
    WHERE s2.Nr = 2 AND s2.KVHx = aa.KVHx) AS Kode2,
    (SELECT Byggeanvendelse FROM aa AS s2 
    WHERE s2.Nr = 2 
    AND s2.KVHx = aa.KVHx) AS Anvend2,
    (SELECT Byggeanvendelseskode FROM aa AS s2
    WHERE s2.Nr = 3 AND s2.KVHx = aa.KVHx) AS Kode3,
    (SELECT Byggeanvendelse FROM aa AS s2
WHERE s2.Nr = 3 AND s2.KVHx = aa.KVHx) AS Anvend3
FROM aa AS s1
WHERE Nr = 1

I've tried a different approch, given that it would only have a maximum of three codes and describtions.. Called it code17july in main post.. — PKPX10, Jul 17 '15 at 11:18

score 0 · Answer 1 · edited May 23 '17 at 12:29

It looks like you're actually quite close with your last try, just remove the alias as s1 from the from clause at the end of the query and you should get the result you want - or at least a result that matches the image you showed as desired result (except for one minor change, but the result doesn't seem to match the source data).

But, your query uses several correlated sub-queries which might not be that efficient and can be replaced by case expressions, and thus your query can be reduced to this (which gives the same output):

WITH cte AS (
    SELECT DISTINCT 
       Anvendelseskoder.[Usage Code] AS [Byggeanvendelseskode],
       Anvendelseskoder.[Usage Code Value] AS [Byggeanvendelse],
       HeleDanmark_DAWA.KVHx,
       Nr = ROW_NUMBER() OVER(PARTITION BY HeleDanmark_DAWA.KVHx 
                              ORDER BY Anvendelseskoder.[Usage Code])
    FROM Anvendelseskoder 
    RIGHT JOIN HeleDanmark_DAWA 
     ON Anvendelseskoder.KVHx = HeleDanmark_DAWA.KVHx
    WHERE HeleDanmark_DAWA.postnr=6720 AND Anvendelseskoder.[Usage Code]>0
)

SELECT KVHx, 
    MAX(CASE WHEN Nr = 1 THEN Byggeanvendelseskode END) AS Kode1, 
    MAX(CASE WHEN Nr = 1 THEN Byggeanvendelse END) AS Anvend1,
    MAX(CASE WHEN Nr = 2 THEN Byggeanvendelseskode END) AS Kode2, 
    MAX(CASE WHEN Nr = 2 THEN Byggeanvendelse END) AS Anvend2,
    MAX(CASE WHEN Nr = 3 THEN Byggeanvendelseskode END) AS Kode3, 
    MAX(CASE WHEN Nr = 3 THEN Byggeanvendelse END) AS Anvend3
FROM cte 
GROUP BY KVHx;

If it can be that you have a varying number of columns you might want to look at making the query dynamic. There are several good answers that shows you how, for instance this: Efficiently convert rows to columns in sql server

Make dynamic coloumns in sql server 2014 when duplicated values appear

1 Answers1