I want to pass some columns value in the second row in SQL here is my full query if anyone can help me to solve.
SELECT
tblCustomers.CustName,
tblPurchase.PurchaseID,
ISNULL(tblPurchase.PurInvoiceNo, 0) AS InvoiceNo,
tblPurchase.PurShowName,
tblPurchase.PurNotes,
tblPurchase.PurDepartment,
tblPurchase.PurQuoteId,
ISNULL(tblQuote.DeliveryDate,'') AS DeliveryDate,
tblBranch.BranchCode,
tblBranch.BranchID,
tblPurchaseStatus.PurStatusDesc,
tblPurchase.PurEntered AS IssuedDate,
tblPurchase.PurEntered,
tblEmployee.EmployeeName AS IssuedBy,
tblQuote.SalesRep,
tblQuote.ProjectLead,
tblPurchase.PO_Code,
tblPurchase.PurVendorID,
tblCustomers.CustType,
tblPurchase.PurDeliveryDate,
tblPurchase.PurReturnDate,
ISNULL(tblEmployee_1.EmployeeName, '') AS ApprovedBy,
tblPurchase.PurReferenceNo,
tblPurchase.PurTax,
tblPurchase.PurTaxRate,
tblPurchase.PurTaxExempt,
tblPurchase.CustID,
tblPurchase.PurSubTotal,
ISNULL(tblPurchase.PurQuoteIncome,'') PurQuoteIncome,
ISNULL(tblPurchase.PurQuoteLaborIncome,'') PurQuoteLaborIncome,
tblPurchase.PurLabor,
tblPurchase.PurPoLoss,
'01/01/1900' AS QID,
tblPurchase.PurEquipment,
tblQuote.QuoteTotal - tblQuote.QuoteTaxAmount AS Total,
tblQuote.QuoteRentalID
,[dbo].[SummarySAR]([tblPurchase].[PurchaseID],[tblPurchaseCodes].[PurCode],'R') [EquipmentRental]
,[dbo].[SummarySAR]([tblPurchase].[PurchaseID],[tblPurchaseCodes].[PurCode],'L') [Labor]
,[dbo].[SummarySAR]([tblPurchase].[PurchaseID],[tblPurchaseCodes].[PurCode],'S') [Shipping]
,[dbo].[SummarySAR]([tblPurchase].[PurchaseID],[tblPurchaseCodes].[PurCode],'P') [EquipmentPurchase]
,[dbo].[SummarySAR]([tblPurchase].[PurchaseID],[tblPurchaseCodes].[PurCode],'O') [Others]
,SUM([tblPurchaseSub].[PurSubDiscTotal]) [SumOfPurSubDiscTotal]
,[tblPurchaseSub].[PurSubTaxable]
,([tblPurchase].[PurTaxRate] * SUM([tblPurchaseSub].[PurSubDiscTotal]) ) / 100 [TaxTotal]
,ISNULL(tblQuote.ShipAddr, '') AS ShipAddr,
tblPurchaseCodes.PurCode,
tblPurchaseCodes.PurCodeDescription,
tblPurchase.PurPaid,
ISNULL(tblQuote.ShipAddr2, '') AS ShipAddr2,
ISNULL(tblQuote.ShipCity, '') AS ShipCity,
ISNULL(tblQuote.ShipState, '') AS ShipState,
ISNULL(tblQuote.ShipZip, '') AS ShipZip,
ISNULL(tblCustAddress.AddrStreet, '') AS CustStreet,
ISNULL(tblCustAddress.AddrCity, '') AS CustCity,
ISNULL(tblCustAddress.AddrState, '') AS CustState,
ISNULL(tblCustAddress.AddrZip, '') AS CustZip
,ISNULL((
SELECT SUM(([QuoteSub].DiscountRate * [QuoteSub].Period) + ([QuoteSub].BillQuantity * [QuoteSub].Period))
--[Pursub].PurAssignedCategoryId, SUM([Pursub].PurSubQty) AS SumofPurSubQty, [Pursub].PurSubCategoryDescription, [Cat].IsCategoryMisc, [QuoteSub].QuoteId, SUM([QuoteSub].BillQuantity * [QuoteSub].Period) AS BillQuantity,
--[QuoteSub].CategoryID, [QuoteSub].DiscountRate, ([QuoteSub].DiscountRate * [QuoteSub].Period) AS RatePeriod, [QuoteSub].QuoteSubParentId, [QuoteSub].Quantity, [QuoteSub].QuoteDescription
FROM [dbo].[tblPurchaseSub] [Pursub] WITH (NOLOCK)
INNER JOIN
[dbo].[tblCat] [Cat] WITH (NOLOCK)
ON [Pursub].PurAssignedCategoryId = [Cat].CatId AND
([PurSub].PurAssignedCategoryId > 0) AND
[Cat].CDescription <> 'LABOR PROJECT MANAGER'
INNER JOIN
[dbo].[tblQuoteSub] [QuoteSub] WITH (NOLOCK) ON [QuoteSub].CategoryID = [Cat].CatId
WHERE ([PurSub].PurchaseID = tblPurchaseSub.PurchaseID) AND ([QuoteSub].QuoteId = tblQuote.QuoteID)
), 0) AS [CalculatedQuoteInc]
FROM
dbo.tblPurchase AS tblPurchase WITH (NOLOCK)
INNER JOIN
dbo.tblPurchaseStatus AS tblPurchaseStatus WITH (NOLOCK)
ON tblPurchase.PurStatus = tblPurchaseStatus.PurStatusAutoID
INNER JOIN
dbo.tblCustomers AS tblCustomers WITH (NOLOCK)
ON tblPurchase.CustID = tblCustomers.CustID
LEFT OUTER JOIN
dbo.tblCustAddress AS tblCustAddress WITH (NOLOCK)
ON tblPurchase.CustID = tblCustAddress.CustID
AND tblCustAddress.AddrTypeID = 1
INNER JOIN
dbo.tblBranch AS tblBranch WITH (NOLOCK)
ON tblPurchase.PurBranchId = tblBranch.BranchID
LEFT OUTER JOIN
dbo.tblEmployee AS tblEmployee WITH (NOLOCK)
ON tblPurchase.PurSalesRep = tblEmployee.EmployeeID
LEFT OUTER JOIN
dbo.tblEmployee AS tblEmployee_1 WITH (NOLOCK)
ON tblPurchase.PurApprovedBy = tblEmployee_1.EmployeeID
LEFT OUTER JOIN
dbo.tblQuote AS tblQuote WITH (NOLOCK)
ON tblPurchase.PurQuoteId = tblQuote.QuoteID
LEFT JOIN
dbo.tblPurchaseSub AS tblPurchaseSub WITH (NOLOCK)
ON tblPurchase.PurchaseID = tblPurchaseSub.PurchaseID
LEFT JOIN
dbo.tblPurchaseCodes AS tblPurchaseCodes WITH (NOLOCK)
ON tblPurchaseSub.PurSubtype = tblPurchaseCodes.PurCode
LEFT OUTER JOIN
dbo.tblPOLossOptions AS tblPOLossOptions WITH (NOLOCK)
ON tblPurchase.PO_Code = tblPOLossOptions.PO_ID
WHERE [tblPurchase].[PurchaseID] = 21384
GROUP BY
tblCustomers.CustName,
tblPurchase.PurchaseID,
ISNULL(tblPurchase.PurInvoiceNo, 0),
tblPurchase.PurShowName,
tblPurchase.PurNotes,
tblPurchase.PurDepartment,
tblPurchase.PurQuoteId,
tblQuote.DeliveryDate,
tblBranch.BranchCode,
tblBranch.BranchID,
tblPurchaseStatus.PurStatusDesc,
tblPurchase.PurEntered ,
tblPurchase.PurEntered,
tblEmployee.EmployeeName,
tblQuote.SalesRep,
tblQuote.ProjectLead,
tblPurchase.PO_Code,
tblPurchase.PurVendorID,
tblCustomers.CustType,
tblPurchase.PurDeliveryDate,
tblPurchase.PurReturnDate,
ISNULL(tblEmployee_1.EmployeeName, ''),
tblPurchase.PurReferenceNo,
tblPurchase.PurTax,
tblPurchase.PurTaxRate,
tblPurchase.PurTaxExempt,
tblPurchase.CustID,
tblPurchase.PurSubTotal,
tblPurchase.PurQuoteIncome,
tblPurchase.PurQuoteLaborIncome,
tblPurchase.PurLabor,
tblPurchase.PurPoLoss,
tblPurchase.PurEquipment,
tblPurchaseSub.PurSubTaxable,
ISNULL(tblQuote.ShipAddr, '') ,
tblPurchaseCodes.PurCode,
tblPurchaseCodes.PurCodeDescription,
tblPurchase.PurPaid,
ISNULL(tblQuote.ShipAddr2, '') ,
ISNULL(tblQuote.ShipCity, '') ,
ISNULL(tblQuote.ShipState, ''),
ISNULL(tblQuote.ShipZip, ''),
ISNULL(tblCustAddress.AddrStreet, ''),
ISNULL(tblCustAddress.AddrCity, ''),
ISNULL(tblCustAddress.AddrState, ''),
ISNULL(tblCustAddress.AddrZip, '')
,tblQuote.QuoteID,
tblPurchaseSub.PurchaseID,
tblQuote.QuoteTotal - tblQuote.QuoteTaxAmount ,
tblQuote.QuoteRentalID
Just a simple word first row this column can be transferred in the second row?
- [EquipmentRental]
- [Labor]
- [Shipping]
- [EquipmentPurchase]
- [Others]
Current result
CustName PurchaseID InvoiceNo PurShowName PurNotes PurDepartment PurQuoteId DeliveryDate BranchCode BranchID PurStatusDesc IssuedDate PurEntered IssuedBy SalesRep ProjectLead PO_Code PurVendorID CustType PurDeliveryDate PurReturnDate ApprovedBy PurReferenceNo PurTax PurTaxRate PurTaxExempt CustID PurSubTotal PurQuoteIncome PurQuoteLaborIncome PurLabor PurPoLoss QID PurEquipment Total QuoteRentalID EquipmentRental Labor Shipping EquipmentPurchase Others SumOfPurSubDiscTotal PurSubTaxable TaxTotal ShipAddr PurCode PurCodeDescription PurPaid ShipAddr2 ShipCity ShipState ShipZip CustStreet CustCity CustState CustZip CalculatedQuoteInc Sequance
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ----------- ----------------------- -------------------------------------------------- ----------- ------------------------------ ----------------------- ----------------------- -------------------------------------------------- ----------- ----------- ----------- ----------- -------------------------------------------------- ----------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- --------------------- --------------------- ------------ ----------- --------------------- --------------------- --------------------- --------------------- --------- ---------- --------------------- --------------------- ------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------- ------------- --------------------- ---------------------------------------------------------------------------------------------------- ------- -------------------------------------------------- ------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- ---------------------------------------- -------------------------------------------------- ---------------------- --------------------
ARNOLD ASSOC 1 0 Show2005 test --Select One-- 3000 2004-03-01 06:30:00.000 LAX 1 CLOSED 2005-02-17 16:08:24.000 2005-02-17 16:08:24.000 NULL 16 NULL NULL 0 Vendor 2005-02-17 00:00:00.000 2005-02-24 00:00:00.000 TEST 0.00 8.25 0 529566581 75.00 0.00 0.00 75.00 NULL 01/01/1900 0.00 725.00 5230 0.00 75.00 0.00 0.00 0.00 75.00 0 6.1875 Holiday Inn, 999 S Main St. L Labor NULL SALT LAKE CITY UT 84111 1737 COMPROMOISE LINE GLENDORA CA 91741 0 1
AUTOMATED BUSINESS PRODUCTS 3 0 Sundance Channel payment on 02/28/2005- cc amex 72005. hp. Operations 7522 2005-01-17 00:00:00.000 SLC 57 CLOSED 2005-02-22 10:41:30.000 2005-02-22 10:41:30.000 Jared Hunt 85 NULL NULL 0 AV Company 2005-01-17 00:00:00.000 2005-01-31 00:00:00.000 Lalit Shah 7475 0.00 6.85 1 -2011940121 615.26 0.00 0.00 0.00 0 01/01/1900 600.00 3552.00 11306 0.00 0.00 0.00 0.00 15.26 15.26 0 1.0453 Claimjumper 573 Main Street O Other 1 PARK CITY UT 84068 385 WEST 2880 SOUTH SLC UT 84115 0 1
AUTOMATED BUSINESS PRODUCTS 3 0 Sundance Channel payment on 02/28/2005- cc amex 72005. hp. Operations 7522 2005-01-17 00:00:00.000 SLC 57 CLOSED 2005-02-22 10:41:30.000 2005-02-22 10:41:30.000 Jared Hunt 85 NULL NULL 0 AV Company 2005-01-17 00:00:00.000 2005-01-31 00:00:00.000 Lalit Shah 7475 0.00 6.85 1 -2011940121 615.26 0.00 0.00 0.00 0 01/01/1900 600.00 3552.00 11306 600.00 0.00 0.00 0.00 0.00 600.00 0 41.10 Claimjumper 573 Main Street R Equipment Rental 1 PARK CITY UT 84068 385 WEST 2880 SOUTH SLC UT 84115 0 2
BEAT VIDEO PRODUCTIONS 1003 0 SHORECLIFF COMMUNICATIONS payment on 04/06/05- chk# 3445. hp Operations 7569 2005-02-28 00:00:00.000 LAX 1 CLOSED 2005-03-18 09:45:50.000 2005-03-18 09:45:50.000 Mark Davis 89 NULL NULL 0 Vendor 2005-02-27 00:00:00.000 2005-03-04 00:00:00.000 Heena Patel 0.00 7.25 1 1727531188 5119.50 0.00 0.00 4657.50 0 01/01/1900 0.00 42470.00 12315 0.00 4657.50 0.00 0.00 0.00 4657.50 0 337.6687 Gaylord Texan (817) 778-1000 L Labor 1 1501 Gaylord Trail GRAPEVINE TX 76051 35 RIVERWALK WAY, UNIT 221 LOWELL MA 01854 0 1
BEAT VIDEO PRODUCTIONS 1003 0 SHORECLIFF COMMUNICATIONS payment on 04/06/05- chk# 3445. hp Operations 7569 2005-02-28 00:00:00.000 LAX 1 CLOSED 2005-03-18 09:45:50.000 2005-03-18 09:45:50.000 Mark Davis 89 NULL NULL 0 Vendor 2005-02-27 00:00:00.000 2005-03-04 00:00:00.000 Heena Patel 0.00 7.25 1 1727531188 5119.50 0.00 0.00 4657.50 0 01/01/1900 0.00 42470.00 12315 0.00 0.00 0.00 0.00 462.00 462.00 0 33.495 Gaylord Texan (817) 778-1000 O Other 1 1501 Gaylord Trail GRAPEVINE TX 76051 35 RIVERWALK WAY, UNIT 221 LOWELL MA 01854 0 2
Expected result Just want to merge repeated rows in a single row and pass some columns value in merge rows.
In the above image, you can see PurchaseID 1,3,3,1003,1003 repeated some row
so if PurchaseID returns one to more rows then need to merge some specific columns values in single rows PurchaseID wise.
Columns names EquipmentRental,Labor,Shipping,EquipmentPurchase,Others
