Column Headers as Value

[Irons182]

Hi all,

I have a table set out as below. I am trying to get the column headers separated and into rows. Any help greatly appreciated!

Thanks

Current:

Date -- Peter_Inbound -- Peter_Outbound -- Andy_Inbound -- Andy_Outbound
1/1/2017 -- 5 -- 10 -- 16 -- 4
2/1/2017 -- 7 -- 16 -- 12 -- 9
etc...

Desired output:

Date -- Channel -- Name -- Sales
1/1/2017 -- Inbound -- Peter -- 5
1/1/2017 -- Outbound-- Peter -- 10
1/1/2017 -- Inbound -- Andy -- 16
1/1/2017 -- Outbound-- Andy -- 4
2/1/2017 -- Inbound -- Peter -- 7
2/1/2017 -- Outbound -- Peter -- 16
2/1/2017 -- Inbound -- Andy -- 12
2/1/2017 -- Outbound-- Andy -- 9

 

[almahmood]

Use UNION query to get your desired output.

 

[Irons182]

Use UNION query to get your desired output.

Thanks, any chance you could give me an example? I've tried UnionAll but can only get one column as I want it.

 

[arnelgp]

your table is not normalized.

table1:

Date-------DateTime
Inbound----Yes/No
Channel----Short Text (Peter or Andy)
Sales

anyway, if you are unable to do the above,
you need a Union query to accomplished your
request. Replace Table5 on the Query with
your table name.

SELECT Table5.Date, "Inbound" AS Channel, "Peter" AS Name, Table5.Peter_Inbound As Sales, 1 As Seqn
FROM Table5
UNION ALL
SELECT Table5.Date, "Outbound" AS Channel, "Peter" AS Name, Table5.Peter_Outbound, 2 As Seqn
FROM Table5
UNION ALL
SELECT Table5.Date, "Inbound" AS Channel, "Andy" AS Name, Table5.Andy_Inbound, 3 As Seqn
FROM Table5
UNION ALL
SELECT Table5.Date, "Outbound" AS Channel, "Andy" AS Name, Table5.Andy_Outbound, 4 As Seqn
FROM Table5 Order by 1,5

 

[almahmood]

Here you go:

[Date], 'Inbound' as Channel,'Peter' as [Name], [Peter_Inbound] as Sales
union all [Date], 'Outbound' as Channel,'Peter' as [Name], [Peter_Outbound] as Sales
union all [Date], 'Inbound' as Channel,'Andy' as [Name], [Andy_Inbound] as Sales
union all [Date], 'Outbound' as Channel,'Andy' as [Name], [Andy_Outbound] as Sales

 

[Irons182]

Great thanks!

If I wanted to sum values, albeit one might be null i am getting an aggregate error with the following statement.

SELECT  [TBL: Sales].[Transaction Date], "Online" AS Channel, "Peter" AS Name,  "PlusBD" AS Product, Sum(Nz([Peter_Online_PlusBD])+Nz([Peter_Online_PlusLE])) As Sales

 

[arnelgp]

Remove the Colon ( from your
table name. SQL is interpreting
it as Alias (eg)

Channel: "Online"

the query would look something like this:

SELECT [TBL Sales].[Transaction Date], "Online" AS Channel, "Peter" AS Name, "PlusBD" AS Product, Sum(Nz([Peter_Online_PlusBD],0)+Nz([Peter_Online_PlusLE],0)) AS Sales
FROM [TBL Sales]
GROUP BY [TBL Sales].[Transaction Date], "Online", "Peter", "PlusBD";