troubleshooting Question

Move SQL Columns

Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
3 Comments2 Solutions215 ViewsLast Modified:
Hello,

I have the select statement below -

select
M.ID MemberID,
E.[Exchequer-Code] Exchequer,
M.FirstName +' '+ M.LastName Member,
M.ClubName Club,
O.OrderID OrderID,
CONVERT(VARCHAR,O.OrderDate,103) OrderDate,
D.ProductSKU SKU,
N.Code,
D.ProductName Product,
D.Price,
D.Quantity,
D.TotalPrice,
ISNULL(O.OrderSubTotal,0.00) OrderSubTotal,
ISNULL(O.OrderDeliveryCharge,0.00)Carriage,
O.CarriageNotes,
ISNULL(O.OrderDiscount, 0.00) OrderDiscount,
O.OrderDiscountReason,
ISNULL(O.OrderTax,0.00) OrderVAT,
ISNULL(O.OrderTotal,0.00) OrderGrandTotal
from dbo.Ordertb O
inner join dbo.OrderDetail D
on D.OrderID = O.OrderID
inner join dbo.[Direct-Product] P
on P.ID = D.ProductID
inner join dbo.Member M
on M.ID = O.MemberID
inner join dbo.[Member-Exchequer-Codes] E
on E.MemberID = M.ID
inner join dbo.[Nominal-Codes] N 
on N.ID = P.NominalCodeID

Which Produces a table like So ---

MemberID,Exchequer,Member,Club,OrderID,OrderDate,SKU,Code,Product,Price,Quantity,TotalPrice,OrderSubTotal,Carriage,CarriageNotes,OrderDiscount,OrderDiscountReason,OrderVAT,OrderGrandTotal

(I have Comma seperated the column headers)

What I would really like to do though is have this --

MemberID,Exchequer,Member,Club,OrderID,OrderDate,SKU,Code,Product,Price,Quantity,TotalPrice,Notes,OrderSubTotal,OrderVAT,OrderGrandTotal

(Removed - 'Carriage,CarriageNotes,OrderDiscount,OrderDiscountReason' - Added 'Notes')

but have the missing columns as rows beneath so for Carriage -

Insert a row and add these values to the appropriate columns -
SKU = 'Carriage', Code = 'Carriage', Product = 'Carriage', Price = The Variable Carriage, Quantity = 1 TotalPrice =  The Variable -- Carriage, Notes = The Variable -- CarriageNotes

Effectively Carriage & Discounts become rows instead of columns....

Is this possible?

Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros