The Longest SQL query i ever seen credit goes to Farrukh
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT purchase_id, SUM(CASE P.Name WHEN 'Pee Green' THEN P.quantity END) AS 'Pee Green',
SUM(CASE P.Name WHEN 'Purple Haze-Glow' THEN P.quantity END) AS 'Purple Haze-Glow',
SUM(CASE P.Name WHEN 'Testing Product' THEN P.quantity END) AS 'Testing Product', SUM(CASE P.Name WHEN 'TESTPRO1' THEN P.quantity END)
AS 'TESTPRO1', SUM(CASE P.Name WHEN 'T-Shirt Large' THEN P.quantity END) AS 'T-Shirt Large',
SUM(CASE P.Name WHEN 'T-Shirt Medium' THEN P.quantity END) AS 'T-Shirt Medium', SUM(CASE P.Name WHEN 'T-Shirt Small' THEN P.quantity END)
AS 'T-Shirt Small', SUM(CASE P.Name WHEN 'T-Shirt XL' THEN P.quantity END) AS 'T-Shirt XL',
SUM(CASE P.Name WHEN 'X Green - Glow' THEN P.quantity END) AS 'X Green - Glow'
FROM Query3 AS P
GROUP BY purchase_id


select

detail.id ,detail.Date ,detail.user_id ,detail.CCardNo ,detail.CCHolderFirstName ,detail.CCHolderLastName
,detail.CCType ,detail.ExpMonth ,detail.ExpYear ,detail.ShipOptionPrice
,detail.ShipOptionShirtAddition ,detail.LPResponse ,detail.DiscountDescription ,detail.DiscountAmount
,detail.CCCVV ,detail.ShipOptionTabzAddition ,detail.ShipOptionMaxTabz ,detail.ShipAccountNumber ,detail.ShipDescription
,detail.shipaddress1 ,detail.shipfname ,detail.shiplname ,detail.shipaddress2 ,detail.shipcity
,detail.shipstate ,detail.shipzip ,detail.shipcountry ,detail.shipphone
,detail.OrderStatusName ,detail.ShipOption ,detail.[Purple_Haze-Glow] ,detail.[Testing_Product]
,detail.[TESTPRO1] ,detail.[T-Shirt_Large] ,detail.[T-Shirt_Medium] ,detail.[T-Shirt_Small]
,detail.[T-Shirt_XL] ,detail.[X_Green_-_Glow]

,case detail.shipstate when 'TX' then '0.0725' else '0' end as 'Tax',
isnull(cost.cost-REPLACE(detail.discountamount,'$',''),cost.cost) as 'Net Amount',
isnull(cost.cost-REPLACE(detail.discountamount,'$',''),cost.cost) *
case detail.shipstate when 'TX' then '0.0725' else '0' end +
isnull(cost.cost-REPLACE(detail.discountamount,'$',''),cost.cost) as 'Grand Total'

from

(
select purchase_id , sum(price * quantity) as 'Cost'
from purchase_items
group by purchase_id, price * quantity with rollup
having grouping(price * quantity)=1
) as cost ,
(
SELECT dbo.purchases.id, CONVERT(VARCHAR, MONTH(dbo.purchases.dt)) + '/' + CONVERT(VARCHAR, DAY(dbo.purchases.dt)) + '/' + CONVERT(VARCHAR,
YEAR(dbo.purchases.dt)) + ' ' + CONVERT(CHAR(8), dbo.purchases.dt, 8) AS Date, dbo.purchases.user_id, dbo.purchases.CCardNo,
dbo.purchases.CCHolderFirstName, dbo.purchases.CCHolderLastName, dbo.purchases.CCType, dbo.purchases.ExpMonth, dbo.purchases.ExpYear,
dbo.purchases.ShipOptionPrice, dbo.purchases.ShipOptionShirtAddition, dbo.purchases.LPResponse, dbo.purchases.DiscountDescription,
'$' + REPLACE(REPLACE(dbo.purchases.DiscountAmount, '^$', ''), '^%', '') AS DiscountAmount, dbo.purchases.CCCVV,
dbo.purchases.ShipOptionTabzAddition, dbo.purchases.ShipOptionMaxTabz, dbo.purchases.ShipAccountNumber, dbo.purchases.ShipDescription,
dbo.Shipping.shipaddress1, dbo.Shipping.shipfname, dbo.Shipping.shiplname, dbo.Shipping.shipaddress2, dbo.Shipping.shipcity,
dbo.Shipping.shipstate, dbo.Shipping.shipzip, dbo.Shipping.shipcountry, dbo.Shipping.shipphone, dbo.OrderStatus.OrderStatusName,
dbo.Shipping_Methods.Method_Name AS ShipOption, dbo.Pivot2.[Pee Green] AS Pee_Green, dbo.Pivot2.[Purple Haze-Glow] AS 'Purple_Haze-Glow',
dbo.Pivot2.[Testing Product] AS Testing_Product, dbo.Pivot2.TESTPRO1 AS TESTPRO1, dbo.Pivot2.[T-Shirt Large] AS 'T-Shirt_Large',
dbo.Pivot2.[T-Shirt Medium] AS 'T-Shirt_Medium', dbo.Pivot2.[T-Shirt Small] AS 'T-Shirt_Small', dbo.Pivot2.[T-Shirt XL] AS 'T-Shirt_XL',
dbo.Pivot2.[X Green - Glow] AS 'X_Green_-_Glow'
FROM dbo.purchases INNER JOIN
dbo.OrderStatus ON dbo.purchases.OrderStatus = dbo.OrderStatus.OrderStatusID INNER JOIN
dbo.Pivot2 ON dbo.purchases.id = dbo.Pivot2.purchase_id LEFT OUTER JOIN
dbo.Shipping_Methods ON dbo.purchases.ShipOption = dbo.Shipping_Methods.SMID LEFT OUTER JOIN
dbo.Shipping ON dbo.purchases.ShipInformation = dbo.Shipping.ShipID
WHERE (dbo.purchases.dt BETWEEN '02/24/1997' AND '03/02/2012')
) as Detail


where cost.purchase_id=detail.id and detail.Date between detail.Date and detail.Date
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

We were facing an issue in" battle of the band "site about formating a text.all we have to do is to pad 0 to the string if we will not do this it will give us fictitious result.so how we solved it in C# is describe below


double dValue =double.Parse((lb.Text).ToString());
string s = System.String.Format("{0:f}",dValue).ToString();