Unpivot With Column Name

Unpivot with column name

Your query is very close. You should be able to use the following which includes the subject in the final select list:

select u.name, u.subject, u.marks
from student s
unpivot
(
  marks
  for subject in (Maths, Science, English)
) u;

See SQL Fiddle with demo

Unpivoting with Column headers - single row

You can use the UNPIVOT like following query.

;WITH cte 
     AS (SELECT C.customer_name, 
                CC.contact_name, 
                CC.telephone, 
                CC.fax, 
                CC.contact_initials, 
                CC.contact_firstname, 
                CC.email, 
                CC.contact_dear, 
                CC.numeric_phone_no, 
                CC.telephone_number2, 
                CC.mobile_telephone, 
                CC.numeric_telephone2, 
                CC.numeric_mobile, 
                CC.numeric_fax, 
                CC.contact_full_name, 
                contact_middle_names 
         FROM   table C 
                INNER JOIN table CC 
                        ON C.COLUMN = CC.COLUMN 
         WHERE  C.COLUMN = @CustomerAccount) 
SELECT u.x AS ColumnName, 
       u.y AS ColumnValue 
FROM   cte s 
       UNPIVOT ( [y] 
               FOR [x] IN (customer_name, 
                           contact_name, 
                           telephone, 
                           fax, 
                           contact_initials, 
                           contact_firstname, 
                           email, 
                           contact_dear, 
                           numeric_phone_no, 
                           telephone_number2, 
                           numeric_mobile, 
                           numeric_fax, 
                           contact_full_name, 
                           contact_middle_names) ) u; 

Online Demo

Include Column name in SQL Unpivot

Your syntax looks like SQL Server. If so, just use a APPLY:

SELECT s.id, v.*
FROM Sheet2$ s CROSS APPLY
     (VALUES ('Question1', s.Question1),
             ('Question2', s.Question2),
             ('Question3', s.Question3),
             ('Question4', s.Question4)
     ) v(question, result);
         

Add Columns of Columns name during UNPIVOT

Instead of trying to retrieve the column names, since every 6 rows it is repeated, I just have to manually add the names for every n rows :

SELECT
CASE 
    WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =1 THEN 'sport'
    WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =2 THEN 'yahoo'
    WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =3 THEN 'mobile'
    WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =4 THEN 'di'
    WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =5 THEN 'onet'
    WHEN ROW_NUMBER() over (ORDER BY w_id)%6 =0 THEN 'player'
END AS Label, 
isPlayed, wename, webgue, w_id from
 website
UNPIVOT
(
 isPlayed for names in (sport, yahoo, mobile, di, onet, player)
) temp

Unpivot with column name as date

If you do want to unpivot for this query, you can do it like this:

declare @t table (prod_codigo int,esps_fecini date,day1 decimal(5,2),day2 decimal(5,2),
                  day3 decimal(5,2),day4 decimal(5,2),day5 decimal(5,2),day6 decimal(5,2),
                  day7 decimal(5,2))
insert into @t(prod_codigo,esps_fecini,day1,day2,day3,day4,day5,day6,day7) values
(1077,'20181112',200.00,200.00,250.00,250.00,0.00,0.00,0.00)

select
    prod_codigo,
    newDay,
    Value
from
    @t
        unpivot
    (Value for Offset in (day1,day2,day3,day4,day5,day6,day7)) u
        cross apply
    (select DATEADD(day,CONVERT(int,SUBSTRING(Offset,4,1))-1,esps_fecini) as newDay) v

Where we unpivot first and then work out how to extract a usable number from the resulting data (rather than metadata - the column names) to adjust the date value.

Result:

prod_codigo newDay     Value
----------- ---------- ---------------------------------------
1077        2018-11-12 200.00
1077        2018-11-13 200.00
1077        2018-11-14 250.00
1077        2018-11-15 250.00
1077        2018-11-16 0.00
1077        2018-11-17 0.00
1077        2018-11-18 0.00

Unpivot with column name help needed

Unpivot it using UNION ALL. Unfortunately, Sqlite has nor pivot/unpivot shortcuts. Try

select P.TINNUMBER
      ,SI.InvNumber
      ,SI.TotalAmount
      ,SII.ItemName
      ,SII.Tax
from SalesInvoices SI 
Inner Join user P ON SI.UserID=P.UserID 
Left Outer Join (
  Select InvNumber, ItemName, Tax1 as Tax
  From Item
  Union All
  Select InvNumber, ItemName, Tax2 as Tax
  From Item
  Union All
  Select InvNumber, ItemName, Tax3 as Tax
  From Item
  Union All
  Select InvNumber, ItemName, Tax4 as Tax
  From Item
 ) SII on SI.InvNumber=SII.InvNumber

is possible to unpivot a sql server table using headers as a column and values as another column?

I'd recommend using APPLY to unpivot your table

Unpivot using APPLY

DROP TABLE IF EXISTS #YourTable
CREATE TABLE #YourTable (
     ID INT IDENTITY(1,1) PRIMARY KEY
    ,TableName VARCHAR(100)
    ,Dates Varchar(25)
    ,ModelName VARCHAR(100)
    ,BaseUnitPerPallet TINYINT
    ,Pallet TINYINT
)

INSERT INTO #YourTable
VALUES 
('Calendar','June',NULL,4,1)
,('Country','June',NULL,2,6)
,('Product','June','DOWNSTREAM',NULL,8)
,('ProductBOM','June','DOWNSTREAM',9,9)

SELECT A.ID,B.*
FROM #YourTable AS A
CROSS APPLY 
    (VALUES 
        ('TableName',A.TableName)
        ,('Dates',A.Dates)  
        ,('ModelName',A.ModelName)  
        ,('BaseUnitPerPallet',CAST(A.BaseUnitPerPallet AS Varchar(100)))
        ,('Pallet',CAST(A.Pallet AS Varchar(100)))  
    ) AS B(ColumnName,Val)
--WHERE B.Val IS NOT NULL /*Optional in case you want to ignore NULLs*/
ORDER BY A.ID,B.ColumnName

how to dynamically unpivot only those columns with a specific suffix in bigquery

Additionally, to @Mikhail Answer that is correct you need to add a WHERE clause with a REGEXP_CONTAINS expression as the following one:

where REGEXP_CONTAINS(col, '_next') OR REGEXP_CONTAINS(col,'_last')

The full Query will be:

select * from your_table
unpivot (metric for col in (product_next, upload_last, active_next)) 
where REGEXP_CONTAINS(col, '_next') OR REGEXP_CONTAINS(col,'_last')  

---

Related Topics