Change layout of view - SQL

Question

I have a view which has balances for nominal codes.

I have the nominal code on each row with the periods Jan-Dec in each column

I would like to change so that each nominal code has 12 rows (1 for each month)

I am creating the query SQL Server Management Studio - This view can then be pulled into Sage. The current view is

SELECT TOP (1000) [AccountNumber]
  ,[Jan17]
  ,[Feb17]
  ,[Mar17]
  ,[Apr17]
  ,[May17]
  ,[Jun17]
  ,[Jul17]
  ,[Aug17]
  ,[Sep17]
  ,[Oct17]
  ,[Nov17]
  ,[Dec17]
From Sage_200.dbo.NominalBalance

I require each month to have a row for each nominal code instead of one row and each month having its own column.

Thanks

Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. — , Oct 05 '17 at 08:31

score 0 · Answer 1 · answered Oct 05 '17 at 11:38

Here's a good link SQL Server : Columns to Rows Based on your question an unpivot could look like this.

USE SANDBOX
DROP TABLE T
CREATE TABLE T(CDE INT,JAN INT,FEB INT,MAR INT,APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT)
GO

TRUNCATE TABLE T
INSERT INTO T VALUES (111,1,2,3,4,5,6,7,8,9,10,11,12),(222,13,14,15,16,17,18,19,20,21,22,23,24)

SELECT CDE,MTH,VALUE
 FROM T
 UNPIVOT (VALUE FOR MTH IN (JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)) UPVT

Result

CDE              VALUE
----------- ---- -----------
111         JAN  1
111         FEB  2
111         MAR  3
111         APR  4
111         MAY  5
111         JUN  6
111         JUL  7
111         AUG  8
111         SEP  9
111         OCT  10
111         NOV  11
111         DEC  12
222         JAN  13
222         FEB  14
222         MAR  15
222         APR  16
222         MAY  17
222         JUN  18
222         JUL  19
222         AUG  20
222         SEP  21
222         OCT  22
222         NOV  23
222         DEC  24

(24 row(s) affected)

Change layout of view - SQL

1 Answers1