SQL unpivot function












0















i have a table with over 100 rows and 20 columns ( store, sales, size, profit etc.) how can i unpivot into just three columns ( store, metric , value)



current state



current state



future state



future state






sql sql-server tsql unpivot







share|improve this question

























  • Which DBMS are you using?

    – Mayank Porwal
    Nov 24 '18 at 19:29











  • ms sql server is the DBMS

    – PRC
    Nov 24 '18 at 21:17
















0















i have a table with over 100 rows and 20 columns ( store, sales, size, profit etc.) how can i unpivot into just three columns ( store, metric , value)



current state



current state



future state



future state






sql sql-server tsql unpivot







share|improve this question

























  • Which DBMS are you using?

    – Mayank Porwal
    Nov 24 '18 at 19:29











  • ms sql server is the DBMS

    – PRC
    Nov 24 '18 at 21:17














0












0








0








i have a table with over 100 rows and 20 columns ( store, sales, size, profit etc.) how can i unpivot into just three columns ( store, metric , value)



current state



current state



future state



future state






sql sql-server tsql unpivot







share|improve this question
















i have a table with over 100 rows and 20 columns ( store, sales, size, profit etc.) how can i unpivot into just three columns ( store, metric , value)



current state



current state



future state



future state






sql sql-server tsql unpivot




sql sql-server tsql unpivot






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 25 '18 at 13:25









a_horse_with_no_name

301k46459554




301k46459554










asked Nov 24 '18 at 19:26









PRCPRC

82




82













  • Which DBMS are you using?

    – Mayank Porwal
    Nov 24 '18 at 19:29











  • ms sql server is the DBMS

    – PRC
    Nov 24 '18 at 21:17



















  • Which DBMS are you using?

    – Mayank Porwal
    Nov 24 '18 at 19:29











  • ms sql server is the DBMS

    – PRC
    Nov 24 '18 at 21:17

















Which DBMS are you using?

– Mayank Porwal
Nov 24 '18 at 19:29





Which DBMS are you using?

– Mayank Porwal
Nov 24 '18 at 19:29













ms sql server is the DBMS

– PRC
Nov 24 '18 at 21:17





ms sql server is the DBMS

– PRC
Nov 24 '18 at 21:17












1 Answer
1






active

oldest

votes


















1














I recommend using cross apply:



select t.store, v.metric, v.value

from t cross apply

     (values ('size', size),

             ('sales', sales),

             ('profit', profit),

             . . .

     ) v(metric, value);


Note that this requires that the value column have compatible types.



Although you do have to list all the columns, you can generate the list using a query or a spreadsheet, simplifying the effort to write the query.






share|improve this answer
























  • thank you. this works great

    – PRC
    Nov 25 '18 at 16:54











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461642%2fsql-unpivot-function%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














I recommend using cross apply:



select t.store, v.metric, v.value

from t cross apply

     (values ('size', size),

             ('sales', sales),

             ('profit', profit),

             . . .

     ) v(metric, value);


Note that this requires that the value column have compatible types.



Although you do have to list all the columns, you can generate the list using a query or a spreadsheet, simplifying the effort to write the query.






share|improve this answer
























  • thank you. this works great

    – PRC
    Nov 25 '18 at 16:54
















1














I recommend using cross apply:



select t.store, v.metric, v.value

from t cross apply

     (values ('size', size),

             ('sales', sales),

             ('profit', profit),

             . . .

     ) v(metric, value);


Note that this requires that the value column have compatible types.



Although you do have to list all the columns, you can generate the list using a query or a spreadsheet, simplifying the effort to write the query.






share|improve this answer
























  • thank you. this works great

    – PRC
    Nov 25 '18 at 16:54














1












1








1







I recommend using cross apply:



select t.store, v.metric, v.value

from t cross apply

     (values ('size', size),

             ('sales', sales),

             ('profit', profit),

             . . .

     ) v(metric, value);


Note that this requires that the value column have compatible types.



Although you do have to list all the columns, you can generate the list using a query or a spreadsheet, simplifying the effort to write the query.






share|improve this answer













I recommend using cross apply:



select t.store, v.metric, v.value

from t cross apply

     (values ('size', size),

             ('sales', sales),

             ('profit', profit),

             . . .

     ) v(metric, value);


Note that this requires that the value column have compatible types.



Although you do have to list all the columns, you can generate the list using a query or a spreadsheet, simplifying the effort to write the query.







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 25 '18 at 13:24









Gordon LinoffGordon Linoff

782k35310414




782k35310414













  • thank you. this works great

    – PRC
    Nov 25 '18 at 16:54



















  • thank you. this works great

    – PRC
    Nov 25 '18 at 16:54

















thank you. this works great

– PRC
Nov 25 '18 at 16:54





thank you. this works great

– PRC
Nov 25 '18 at 16:54




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461642%2fsql-unpivot-function%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Costa Masnaga

Image
.mw-parser-output .avviso .mbox-text-div>div,.mw-parser-output .avviso .mbox-text-full-div>div{font-size:90%}.mw-parser-output .avviso .mbox-image div{width:52px}.mw-parser-output .avviso .mbox-text-full-div .hide-when-compact{display:block} Questa voce o sezione sull'argomento Lombardia non cita le fonti necessarie o quelle presenti sono insufficienti . Puoi migliorare questa voce aggiungendo citazioni da fonti attendibili secondo le linee guida sull'uso delle fonti. Segui i suggerimenti del progetto di riferimento. Costa Masnaga comune Localizzazione Stato   Italia Regione Lombardia Provincia Lecco Amministrazione Sindaco Sabina Panzeri (Al centro il cittadino) dall'08/06/2009 Territorio Coordinate 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Coordinate: 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Altitudine 318 m s.l.m...
Read more

Fotorealismo

Image
Guerrino Boatto, Venice's St. Barnaba Church and canal Il fotorealismo è un genere di pittura basato sull'uso di una o più fotografie dalle quali l'artista prende le informazioni necessarie da utilizzare poi nel processo della creazione dell'opera, in genere ad olio o ad acrilico, al fine di avere un aspetto finale il più simile possibile alla fotografia. Indice 1 Storia 2 Lista dei fotorealisti 3 Note 4 Voci correlate 5 Altri progetti Storia | Il fotorealismo è un movimento che nasce negli Stati Uniti d'America alla fine degli anni sessanta [1] sulla scia della Pop Art [2] [3] [4] , e insieme condividono il carattere reazionario verso la travolgente ascesa dei media, che alla metà del ventesimo secolo si trasforma in un fenomeno di massa talmente imponente da minacciare il valore dell'immagine nell'arte. [5] [6] Ne consegue così che, colto il gesto d'inizio che fu della Pop Art, i fotorealisti aprono ad un ...
Read more

Sidney Franklin

Image
Questa voce sull'argomento registi statunitensi è solo un abbozzo . Contribuisci a migliorarla secondo le convenzioni di Wikipedia. Sidney Franklin, foto di Carl Van Vechten Oscar alla memoria Irving G. Thalberg 1943 Sidney Franklin , all'anagrafe Sidney Arnold Franklin , (San Francisco, 21 marzo 1893 – Santa Monica, 18 maggio 1972), è stato un regista, produttore cinematografico e attore statunitense. Il 20 giugno 1963, si sposò con l'attrice australiana Enid Bennett. Il matrimonio durò fino alla morte dell'attrice, il 14 maggio 1969 [1] Indice 1 Filmografia 1.1 Regista 1.2 Aiuto regia (parziale) 1.3 Produttore 1.4 Attore (parziale) 2 Note 3 Altri progetti 4 Collegamenti esterni Filmografia | Regista | The Baby , co-regia Chester M. Franklin (1915) The Rivals , co-regia Chester M. Franklin (1915) Little Dick's First Case , co-regia Chester M. Franklin (1915) Her Filmland Hero , co-...
Read more