--DROP TABLE TempReportJodie2
SELECT tbo.OrganisationID ,tbo.TradingName,tbo.LongName,tbo.ABN,
CASE WHEN tbo.Status='111' THEN 'Active' WHEN tbo.status='112' THEN 'In-Active' ELSE '' END AS [Status],
tbe.FirstName+' '+tbe.LastName AS [Sales Person],
tbl.LocType,tbl.LOCAddress1 AS [LOCAddress1 (Address)],tbl.LOCAddress2,tbl.LOCAddress3,tbl.LOCName AS [LOCName (Sub urb)],
tbl.LOCState AS [LOCState (State code)],tbl.LOCPostCode AS [LOCPostCode (Postcode)],tbl.Phone,tbl.PhoneAlt,
tbl.Fax,tbl.Mobile,tbl.HomeEmail,tbl.WorkEmail,tbl.Other1,tbl.Other2
INTO TempReportJodie2
FROM dbo.tbOrganisation tbo
LEFT JOIN dbo.tbLocations tbl ON tbl.LocID=tbo.OrganisationID
LEFT JOIN dbo.tbEmployee tbe ON tbe.EmployeeID=tbo.salespersonid AND chkSalesperson=1
WHERE chkClient=1
ORDER BY tbo.OrganisationID
ALTER TABLE TempReportJodie2 ADD ID INT IDENTITY(1,1)
alter table TempReportJodie2 add order1 int
update TempReportJodie2
set order1 =1+isnull((select count(*) from TempReportJodie2 where rganisationID=a.OrganisationID and ID < a.ID),0)
from TempReportJodie2 as a
--8283
SELECT tj.*,tj1.LocType,tj1.[LOCAddress1 (Address)],tj1.LOCAddress2,tj1.LOCAddress3,tj1.[LOCName (Sub urb)],tj1.[LOCState (State code)],tj1.[LOCPostCode (Postcode)]
,tj2.LocType,tj2.[LOCAddress1 (Address)] ,tj2.LOCAddress2,tj2.LOCAddress3,tj2.[LOCName (Sub urb)],tj2.[LOCState (State code)],tj2.[LOCPostCode (Postcode)] FROM TempReportJodie2 tj
LEFT JOIN TempReportJodie2 tj1 ON tj.OrganisationID=tj1.OrganisationID AND tj1.order1=2
LEFT JOIN TempReportJodie2 tj2 ON tj.OrganisationID=tj2.OrganisationID AND tj2.order1=3
WHERE tj.order1=1
----other way
DROP TABLE ##first
DROP TABLE ##second2
DROP TABLE ##third
SELECT a.* INTO ##first FROM dbo.tbLocations a
WHERE
LocationID IN (SELECT TOP 1 LocationID FROM dbo.tbLocations WHERE LocID=a.LocID ORDER BY LocationID )
order BY a.LocID
SELECT a.* INTO ##second2 FROM dbo.tbLocations a
WHERE
LocationID IN (SELECT TOP 2 LocationID FROM dbo.tbLocations WHERE LocID=a.LocID ORDER BY LocationID )
AND a.LocationID NOT IN (SELECT LocationID FROM ##first)
order BY a.LocID
SELECT a.* INTO ##third FROM dbo.tbLocations a
WHERE
LocationID IN (SELECT TOP 3 LocationID FROM dbo.tbLocations WHERE LocID=a.LocID ORDER BY LocationID )
AND a.LocationID NOT IN (SELECT LocationID FROM ##first)
AND a.LocationID NOT IN (SELECT LocationID FROM ##second2)
order BY a.LocID
SELECT oo.OrganisationID ,oo.TradingName,oo.LongName,oo.ABN,oo.Status,oo.SalespersonID,
##first.LocType,##first.LOCAddress1 AS [LOCAddress1 (Address)],##first.LOCAddress2,##first.LOCAddress3,##first.LOCName AS [LOCName (Sub urb)],
##first.LOCState AS [LOCState (State code)],##first.LOCPostCode AS [LOCPostCode (Postcode)],##first.Phone,##first.PhoneAlt,
##first.Fax,##first.Mobile,##first.HomeEmail,##first.WorkEmail,##first.Other1,##first.Other2,
##second2.LocType,##second2.LOCAddress1 AS [LOCAddress1 (Address)],##second2.LOCAddress2,##second2.LOCAddress3,##second2.LOCName AS [LOCName (Sub urb)],
##second2.LOCState AS [LOCState (State code)],##second2.LOCPostCode AS [LOCPostCode (Postcode)],
##third.LocType,##third.LOCAddress1 AS [LOCAddress1 (Address)],##third.LOCAddress2,##third.LOCAddress3,##third.LOCName AS [LOCName (Sub urb)],
##third.LOCState AS [LOCState (State code)],##third.LOCPostCode AS [LOCPostCode (Postcode)]
--##second2.LocType,##second2.LOCAddress1,##second2.LOCAddress2,##second2.LOCAddress3,
--##third.LocType,##third.LOCAddress1,##third.LOCAddress2,##third.LOCAddress3
FROM dbo.tbOrganisation oo
LEFT JOIN ##first ON oo.OrganisationID=##first.LocID
LEFT JOIN ##second2 ON oo.OrganisationID=##second2.LocID
LEFT JOIN ##third ON oo.OrganisationID=##third.LocID
WHERE chkClient=1
ORDER BY oo.OrganisationID
