March 21, 2012 at 1:22 pm
basically I have a sequential index on this table and Im trying to combine the results into 1 column when the data should be grouped together. I figured assigning a variable to each of the columns would be the easiest but Im stuck
basically I have a table like so
ID LAST_NAME FIRST_NAME ADDRESS_LINES POS
123 Doh John 1234 Happy 1
123 Doh John Apt 10 2
I want it so that the address lines contains a concatenation of POS 1 and 2
I have this so far but Im stuck
DECLARE @address_1 varchar(200)
DECLARE @address_2 varchar (200)
DECLARE @ID varchar (10)
SELECT @ID = (ADDRESS_ID from ADDRESS_LS)
SET @address_1 = (SELECT ADDRESS_LINES FROM ADDRESS_LS WHERE ADDRESS_LS.POS = '1' and ADDRESS_ID = @ID)
SET @address_2 = (SELECT ADDRESS_LINES FROM ADDRESS_LS WHERE ADDRESS_LS.POS = '2' and ADDRESS_ID = @ID)
SELECT dbo.PERSON.ID, dbo.PERSON.LAST_NAME, dbo.PERSON.FIRST_NAME, dbo.PERSON.MIDDLE_NAME, dbo.PERSON.PREFIX, dbo.PERSON.PREFERRED_NAME,
dbo.PERSON.SUFFIX, dbo.ADDRESS.STATE, dbo.ADDRESS.CITY, dbo.ADDRESS.ZIP, dbo.ADDRESS.COUNTRY, dbo.ADDRESS.ADDRESS_CORP_NAME,
dbo.ADDRESS_LS.ADDRESS_LINES, dbo.ADDRESS_LS.ADDRESS_ID, dbo.PSEASON.ADDR_TYPE, ADDRESS_LS.POS, @address_1, @address_2
FROM dbo.PERSON INNER JOIN
dbo.ADDRESS ON dbo.PERSON.PREFERRED_ADDRESS = dbo.ADDRESS.ADDRESS_ID INNER JOIN
dbo.ADDRESS_LS ON dbo.ADDRESS.ADDRESS_ID = dbo.ADDRESS_LS.ADDRESS_ID INNER JOIN
dbo.PSEASON ON dbo.ADDRESS.ADDRESS_ID = dbo.PSEASON.PERSON_ADDRESSES
March 21, 2012 at 1:30 pm
Does this help you? You will have to modify it for your use.
WITH CTE AS
(
SELECT
name as TableName,
object_id
FROM sys.tables WHERE schema_id = schema_id('dbo') and name like '%Lookup%'
)
SELECT TableName,
CommaList = STUFF((
SELECT ',' + c.name
FROM sys.columns c
WHERE c.object_id = CTE.object_id
ORDER BY c.column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY Tablename;
March 21, 2012 at 1:35 pm
Try this:
SELECTLAST_NAME,
FIRST_NAME,
[ADDRESS] = (SELECT STUFF((SELECT ' ' + ADDRESS_LINES
FROM yourTable
WHERE ID = a.ID
ORDER BY POS
FOR XML PATH('')
), 1, 1, ''))
FROM yourTable a
March 21, 2012 at 1:40 pm
Wildcat (3/21/2012)
Try this:
SELECTLAST_NAME,
FIRST_NAME,
[ADDRESS] = (SELECT STUFF((SELECT ' ' + ADDRESS_LINES
FROM yourTable
WHERE ID = a.ID
ORDER BY POS
FOR XML PATH('')
), 1, 1, ''))
FROM yourTable a
that worked perfectly
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply