SQL QUERY Move infor 2 second line

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL QUERY Move infor 2 second line

Author

Topic

tyekhan
Starting Member

29 Posts

Posted - 2013-05-30 : 11:19:58

The query below shows me what a customer has brought in a table, the limit is 4 items per customer.

So what I would like to see is that if a customer has brought more than 1 item then the second item to be move to a new row with the first 7 columns + second items details,

if saletype2 is not null to (so then columns 14-30) the first 7 columns would go into the same columns rows , but the next columns from 14-30 would go into the below,

EETMUK2 to go into EETMUK1
ContractLength_02 to go into ContractLength
Device1 into Device

Ect…


SELECT     OverallInfo.ID AS [ID], OverallInfo.CallDate, OverallInfo.CallTime, OverallInfo.CustomerID AS Outcome, 
                      OverallInfo.AgentName, OverallInfo.ContactName, OverallInfo.ClientRef2 AS [Mobile Number], OverallInfo.Postcode, 
                      OverallInfo.PIN, OverallInfo.PricePlan_Orig AS [Original Price Plan], OverallInfo.EETMUK1 AS [Contract Type], 
                      OverallInfo.ContractLength, OverallInfo.Device, OverallInfo.ChosenHandset AS Handset, 
                      OverallInfo.ChosenHandsetOther AS [Handset Other], OverallInfo.COLOURSIZE AS [Handset Specific (Colour/Size)], 
                      OverallInfo.TALKPLANCSSCODE AS [PLAN (CSS)], OverallInfo.DISCOUNTCODE, OverallInfo.MRCDISCOUNT AS [MRC Discount], 
                      OverallInfo.Boosters AS [Plan Detail], OverallInfo.PricePlan AS [Plan Terms], OverallInfo.MonthlyDiscountCost AS [MRC (Inc Discount)], 
                      '' AS [Discounted Amount], OverallInfo.FinalHandset AS FinalHandsetcost, OverallInfo.UpData AS [Date To Collect Payment], 
                      OverallInfo.upTime AS [Time To Collect Payment], OverallInfo.NotesDelivery, OverallInfo.DeliveryAddress01, 
                      OverallInfo.DeliveryAddress02 + N' ' + OverallInfo.DeliveryAddress03 + N' ' + OverallInfo.DeliveryAddress04 + N' ' + OverallInfo.DeliveryAddress05
                       AS DeliveryAddress, OverallInfo.EETMUK2 AS SaleType2, OverallInfo.ContractLength_02, OverallInfo.Device1 AS Device2, 
                      OverallInfo.ChosenHandset_02 AS Handset02, OverallInfo.ChosenHandsetOther_02, 
                      OverallInfo.COLOURSIZE1 AS [Handset Specific (Colour/Size) 02], OverallInfo.TALKPLANCSSCODE1 AS [PLAN (CSS)02], 
                      OverallInfo.DISCOUNTCODE1 AS DISCOUNTCODE02, OverallInfo.MRCDISCOUNT2 AS [MRC Discount2], 
                      OverallInfo.Boosters_02 AS [Plan Detail02], OverallInfo.PricePlan_02 AS [Plan Terms02], 
                      OverallInfo.MonthlyDiscountCost_02 AS [MRC (Inc Discount)02], '' AS [Discounted Amount02], 
                      OverallInfo.FinalHandset_02 AS FinalHandsetcost_02, OverallInfo.UpData1 AS [Date To Collect Payment02], 
                      OverallInfo.upTime1 AS [Time To Collect Payment02], OverallInfo.NotesDelivery_02, OverallInfo.DeliveryAddress01_02, 
                      OverallInfo.DeliveryAddress02_02, OverallInfo.DeliveryAddress03_02, OverallInfo.DeliveryAddress04_02, 
                      OverallInfo.DeliveryAddress05_02, OverallInfo.DeliveryAddress06_02, OverallInfo.DeliveryAddress07_02, 
                      OverallInfo.EETMUK3 AS SaleType3, OverallInfo.ContractLength_03, OverallInfo.Device2 AS Device3, 
                      OverallInfo.ChosenHandset_03, OverallInfo.COLOURSIZE2 AS [Handset Specific (Colour/Size)03], 
                      OverallInfo.TALKPLANCSSCODE3 AS [PLAN (CSS)03], OverallInfo.DISCOUNTCODE2 AS DISCOUNTCODE3, 
                      OverallInfo.MRCDISCOUNT3 AS [MRC Discount3], OverallInfo.PricePlan_03 AS [Plan Terms03], 
                      OverallInfo.Boosters_03 AS [Plan Detail03], OverallInfo.MonthlyDiscountCost_03 AS [MRC (Inc Discount)03], '' AS [Discounted Amount03], 
                      OverallInfo.FinalHandset_03 AS FinalHandsetcost03, OverallInfo.UpData3 AS [Date To Collect Payment03], 
                      OverallInfo.upTime3 AS [Time To Collect Payment03], OverallInfo.NotesDelivery_03, OverallInfo.DeliveryAddress01_03, 
                      OverallInfo.DeliveryAddress02_03, OverallInfo.DeliveryAddress03_03, OverallInfo.DeliveryAddress04_03, 
                      OverallInfo.DeliveryAddress05_03, OverallInfo.DeliveryAddress06_03, OverallInfo.DeliveryAddress07_03, 
                      OverallInfo.EETMUK4 AS SaleType4, OverallInfo.ContractLength_04, OverallInfo.Device3 AS Device4, 
                      OverallInfo.ChosenHandset_04, OverallInfo.ChosenHandsetOther_04, OverallInfo.COLOURSIZE3 AS [Handset Specific (Colour/Size)04], 
                      OverallInfo.TALKPLANCSSCODE3 AS [PLAN (CSS)04], OverallInfo.DISCOUNTCODE3 AS DISCOUNTCODE04, 
                      OverallInfo.MRCDISCOUNT4 AS [MRC Discount4], OverallInfo.Boosters_04 AS [Plan Detail04], 
                      OverallInfo.PricePlan_04 AS [Plan Terms04], OverallInfo.MonthlyDiscountCost_04 AS [MRC (Inc Discount)04], '' AS '''Discounted Amount04''', 
                      OverallInfo.FinalHandset_04 AS FinalHandsetcost_04, OverallInfo.UpData4 AS [Date To Collect Payment04], 
                      OverallInfo.upTime4 AS [Time To Collect Payment04], OverallInfo.NotesDelivery_04, OverallInfo.DeliveryAddress01_04, 
                      OverallInfo.DeliveryAddress02_04, OverallInfo.DeliveryAddress03_04, OverallInfo.DeliveryAddress04_04, 
                      OverallInfo.DeliveryAddress05_04, OverallInfo.DeliveryAddress06_04, OverallInfo.DeliveryAddress07_04, 
                      OverallInfo.Customeraccno AS [ENABLE ID]
FROM         OverallInfo INNER JOIN
                      RC ON OverallInfo.LastRC = RC.RC
WHERE     (RC.Success = 1)
ORDER BY DeliveryAddress DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 02:20:23

Can you illustrate the requirement based on sample data and desired output, that would much easier for us to follow rather than ploughing through the code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tyekhan
Starting Member

29 Posts

Posted - 2013-05-31 : 06:02:08

At the moment all details are on one line, as below link,
[url]
https://docs.google.com/spreadsheet/ccc?key=0AigR4slRTzWfdDhkRXJHd21xYVdnaFZEMFczdE84Qmc&usp=sharing
[/url]

There are two tab the first one is the current output the second tab is what i would like as the output

[quote]Originally posted by visakh16

Can you illustrate the requirement based on sample data and desired output, that would much easier for us to follow rather than ploughing through the code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 05:47:54

Cant get link opened

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tyekhan
Starting Member

29 Posts

Posted - 2013-06-01 : 10:13:23

Sorry try again there are two tab the first tab is the current output the second tab is what i would like it to be

https://docs.google.com/spreadsheet/ccc?key=0AigR4slRTzWfdDhkRXJHd21xYVdnaFZEMFczdE84Qmc&usp=sharing

quote:
Originally posted by visakh16

Cant get link opened

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 20:28:46

where's the sample data for us to work on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tyekhan
Starting Member

29 Posts

Posted - 2013-06-02 : 02:59:38

Not sure how to give u the simple data is there a guide on how to put the data on this forum.

quote:
Originally posted by visakh16

where's the sample data for us to work on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 02:19:45

yep

see

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tyekhan
Starting Member

29 Posts

Posted - 2013-06-03 : 07:50:06

ID CallDate CallTime Outcome AgentName ContactName Mobile Number Postcode PIN Original Price Plan Contract Type ContractLength Device Handset Handset Other Handset Specific (Colour/Size) PLAN (CSS) DISCOUNTCODE MRC Discount Plan Detail Plan Terms MRC (Inc Discount) Discounted Amount FinalHandsetcost Date To Collect Payment Time To Collect Payment NotesDelivery DeliveryAddress01 DeliveryAddress SaleType2 ContractLength_02 Device2 Handset02 ChosenHandsetOther_02 Handset Specific (Colour/Size) 02 PLAN (CSS)02 DISCOUNTCODE02 MRC Discount2 Plan Detail02 Plan Terms02 MRC (Inc Discount)02 Discounted Amount02 FinalHandsetcost_02 Date To Collect Payment02 Time To Collect Payment02 NotesDelivery_02 DeliveryAddress01_02 DeliveryAddress02_02 DeliveryAddress03_02 DeliveryAddress04_02 DeliveryAddress05_02 DeliveryAddress06_02 DeliveryAddress07_02 SaleType3 ContractLength_03 Device3 ChosenHandset_03 Handset Specific (Colour/Size)03 PLAN (CSS)03 DISCOUNTCODE3 MRC Discount3 Plan Terms03 Plan Detail03 MRC (Inc Discount)03 Discounted Amount03 FinalHandsetcost03 Date To Collect Payment03 Time To Collect Payment03 NotesDelivery_03 DeliveryAddress01_03 DeliveryAddress02_03 DeliveryAddress03_03 DeliveryAddress04_03 DeliveryAddress05_03 DeliveryAddress06_03 DeliveryAddress07_03 SaleType4 ContractLength_04 Device4 ChosenHandset_04 ChosenHandsetOther_04 Handset Specific (Colour/Size)04 PLAN (CSS)04 DISCOUNTCODE04 MRC Discount4 Plan Detail04 Plan Terms04 MRC (Inc Discount)04 'Discounted Amount04' FinalHandsetcost_04 Date To Collect Payment04 Time To Collect Payment04 NotesDelivery_04 DeliveryAddress01_04 DeliveryAddress02_04 DeliveryAddress03_04 DeliveryAddress04_04 DeliveryAddress05_04 DeliveryAddress06_04 DeliveryAddress07_04
5277737, 28/05/2013, 18:04:30, Daniel Smith, Miss Test Test, 828, HU5 6FF, 5221, 31 (18mth), O Customer, Additional Line, 24 Month Contracts, Handset Sony Xperia J, null, null, ORLDEC12, 5.00MTHLY, 5, 100 Minutes Unlimited Text 100MB Data Email, Standard 15.50, 10.5, null, null, null, null, null, Alternative address (NOT MOVED ADDRESS), Test, O Customer, Additional Line, 24 Month Contracts, Handset Sony Xperia J, null, null, ORLDEC12, 5.00MTHLY, 5, 100 Minutes Unlimited Text 100MB Data Email, Dolphin Standard 15.50, 10.5, null, null, null, null, null, null, null, null, null, null, null, null, O Customer, Additional Line, 24 Month Contracts, Handset Sony Xperia J, null, null, ORLDEC12, 5.00MTHLY, 5, 100 Minutes Unlimited Text 100MB Data Email, Dolphin Standard 15.50, 10.5, null, null, null, null, null, null, null, null, null, null, null, O Customer, Additional Line, 24 Month Contracts, Handset Sony Xperia J, null, null, ORLDEC12, 5.00MTHLY, 5, 100 Minutes Unlimited Text 100MB Data Email, Dolphin Standard 15.50, 10.5, null, null, null, null, null, null, null, null, null, null, null, null,

also you can get more simple data below link,

https://docs.google.com/spreadsheet/ccc?key=0AigR4slRTzWfdDhkRXJHd21xYVdnaFZEMFczdE84Qmc&usp=sharing

yep

see

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources