Query - Pivoting 2 columns into 2 rows

I am familiar with PIVOTS but have done only simple ones of one column into a row.

I have this query:

DECLARE @EndDate DATE SET @EndDate ='20160728' --{?EndDate}  SELECT T1.ItemCode,SUM(T0.IssuedQty) [Litres],SUM(T1.CmpltQty) [Tons] FROM    WOR1 T0 INNER JOIN         OWOR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN         OITM T2 ON T1.ItemCode = T2.ItemCode WHERE   T0.ItemCode ='FURNACE OIL/0001' AND T1.Type ='S' AND          (T1.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, @EndDate), 0)) AND @EndDate) GROUP BY T1.ItemCode 

This returns the results:

Query - Pivoting 2 columns into 2 rows

What do I need to do is to get the following results but I'm unsure how.

Query - Pivoting 2 columns into 2 rows

How do I achieve this?

EDIT: The one highlighted as duplicate is different. It is joining 2 queries for additional columns. I just need 1 query transposed to take 2 columns into rows.

Replay

You could do something like this I suppose:

DECLARE @EndDate DATE
SET @EndDate ='20160728' --{?EndDate}

SELECT
    A.LineType,
    CASE B.ItemCode WHEN 'FG/RM/001' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/001],
    CASE B.ItemCode WHEN 'FG/RM/002' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/002],
    CASE B.ItemCode WHEN 'FG/RM/003' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/003],
    CASE B.ItemCode WHEN 'FG/RM/004' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/004],
    CASE B.ItemCode WHEN 'FG/RM/005' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/005],
    CASE B.ItemCode WHEN 'FG/RM/006' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/006],
    CASE B.ItemCode WHEN 'FG/RM/007' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/007]
FROM
    (
        SELECT 'Litres' AS LineType
    UNION ALL
        SELECT 'Tons' AS LineType
    ) A
CROSS JOIN
    (
        SELECT T1.ItemCode,SUM(T0.IssuedQty) [Litres],SUM(T1.CmpltQty) [Tons]
        FROM    WOR1 T0 INNER JOIN
                OWOR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
                OITM T2 ON T1.ItemCode = T2.ItemCode
        WHERE   T0.ItemCode ='FURNACE OIL/0001' AND T1.Type ='S' AND
                (T1.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, @EndDate), 0)) AND @EndDate)
        GROUP BY T1.ItemCode
    ) B
GROUP BY
    A.LineType

Category: sql Time: 2016-07-28 Views: 0

Related post

  • SQL query to convert columns into rows 2011-09-12

    I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005. my input Data ONAY_ID sip_RECno KULLANICI ONAY 1 1 user1 true 2 1 user2 true 3 1 user3 false 4 1 user4 true Output would be sip_

  • turn columns into rows? 2010-01-29

    I'm working with some results from a database query in excel. Is there a way I can quickly turn two rows of data with many columns into two columns of data with many rows? --------------Solutions------------- Check this page. Shortly, you need to exe

  • Excel Single column into rows, VBA script insight 2012-06-04

    Okay, so much similiar to the below link but mine is a bit different. Paginate Rows into Columns in Excel I have a lot of data in column A, I want to take every 14 to 15 rows and make them a new row with multiple columns. I'm trying to get it into a

  • Group a number of Excel columns into rows 2012-06-15

    Currently, I am working on a column with the content for teacher's comment. I would like to delimit it by the space or comma or punctuation marks between words inside the content. At the same time, I would like to take out each of the words in the co

  • How to easily change columns into rows in Excel? 2012-04-15

    I have a huge Excel table that I need to transform into paragraphs for a Word report, and I can't find an efficient way to do it The source looks like this: And I would ultimately need something like this, i.e. through a pivot table. Note that "Item

  • Transpose columns into rows while importing into Excel 2011-11-09

    I have a text file with many more columns in it than are allowed in Excel, so I would like to transpose the data into columns while importing it into Excel. Any ideas on how I can do this? --------------Solutions------------- EDIT found a better way,

  • Query single column, all rows in Postgres 2D Array 2015-06-03

    Is there any way to fix the query below so I don't have to specify an arbitrary max row count? DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, array_column integer[][] ); insert into array_test (name, arra

  • Converting columns into rows in r 2016-02-01

    I have the below data formed using code test <- data.frame(dis = c(10,20,30,40),dur=c(30,40,60,90),method=c("car","car","Bicycle","Bicycle"),to_lon=c(-1.980,-1.5678,-1.324,-1.456),to_lat=c(55.3009,55.3416,55.1123

  • Columns into rows and rows into columns 2014-11-16

    I need to do this table transformation, but its little hard for me. I have original table wich looks like this and i need to transform the table into this How can i do that ? Here i prepared example database http://sqlfiddle.com/#!3/0f324 Thank you f

  • Converting column into rows 2016-01-27

    I have a column of dates and numbers like this: 24/01/2016 1 0.123 24/01/2016 2 0.121 24/01/2016 3 0.104 24/01/2016 4 0.116 24/01/2016 5 0.091 ... How can I transform this column in excel so it appears in three columns as such: 24/01/2016 1 0.123 24/

  • Reshaping a column into rows, based on equality of corresponding column 2012-07-04

    I have a data frame that follows the below long Pattern: Name MedName Name1 atenolol 25mg Name1 aspirin 81mg Name1 sildenafil 100mg Name2 atenolol 50mg Name2 enalapril 20mg And would like to get below (I do not care if I can get the columns to be nam

  • select columns into rows grouped by columns values 2016-01-23

    I have a table of logins that looks like this: logins table Emp_ID Created | Action 1 20/01/2016 10:44:42 AM login 1 20/01/2016 4:45:49 PM logout 1 20/01/2016 6:30:13 PM logout 1 21/01/2016 8:46:28 AM login 1 21/01/2016 9:46:42 AM login 1 21/01/2016

  • SQL server how to convert column values into rows 2016-01-28

    I will try to explain my question. My table structure: name Code_1 Code_1_value Code_2 Code_2_value Code_3 Code_3_value N1 ABC1 10 ABC2 15 ABC3 6 N2 ABC1 3 NULL NULL BAA1 10 N3 ABC4 5 ABC2 11 ADC3 6 and I would like to convert this values into new fo

  • Breaking WP_Query loop into rows with variable width columns 2015-07-21

    I'd like to create a Boostrap grid of posts where sticky posts take 6 columns (.col-md-6) and normal posts take 3 columns (.col-md-3). Posts to be sorted dynamically. So in one row there might be either: two sticky posts one sticky post followed by t

  • Transpose several columns (of varying lengths) into rows 2013-11-20

    A similar question to mine was asked that concerned transposing multiple columns into rows. However, the particular column format that I am dealing with is slightly different in that my file contains a varying number of columns that sometimes have du

  • Convert data from columns to rows without losing already stored data below? 2012-08-07

    How do I convert values from a column into rows without losing data in existing rows below? For example, I have data in this format: A | B | C | 1 | Apples| Berries | Cakes | 2 | Pies | Crusts | Nuts | I need the following: A. 1| Apples 2| Berries 3|

  • Column to Row and Vice Versa 2014-01-27

    I need to create a new table by combining the contents of two different tables. Is there any way to transform columns into rows and rows into columns in MySQL? I tried using JOIN but I can't seem to transform a column into a row. Please refer to the

  • How to pivot a column into a row? 2014-10-07

    I have an Excel document where all the data is in one column and I would like it to be in one row instead. So, A1 remains at A1, A2 to B1, A3 to C1, A4 to D1 and so on until column A only has one populated cell. Is there a tool or method to do this?

  • Better query to select multiple columns into a single output column? 2014-10-14

    I have a table with several columns which I want to SELECT: SELECT his_name , her_name, other_name FROM foo; Bu, I instead I want to combine the results all into a single column. As an example, I can do this with UNION ALL as SELECT her_name AS name

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.806 (s). 13 q(s)