tags:

views:

24

answers:

3

Hi,

Im using the following query and its giving me the following ouput. What i want is one row although i tried to use cast function but not much success, Can some one advise how i can achive this MS SQL server as in oracle we do have certain functions like stragg

Output : I'm getting the following result

Tenancy No Property NO usercode name       Address   person no  lead tenant
123    1234        12345 MR  Rose   Temp add  1          1  
123    1234        12345 MRS Rose   Temp add  2          0

Want : I want something like this

123    1234        12345 MR  Rose ; MRS ROSE   Temp add  1 ; 0  

Select   distinct(t."prhst-occ-num") as "TENANCY_REF",         
  per."pr-seq-no"   as "PROPERTY-REF-NO" ,         
  pr."user-cde"     as "USER_CDE",         
  **/*CAST( SUBSTRING( stuff ((
   select distinct(',' + per."fnam")
   From   per per ,
                prhst  prh,
                aTNCY   t 
   where  prh."per-num"=per."num"
   and  t."prhst-occ-num"=prh."occ-num"  
   and  prh."lead-tenant" in (0,1)
   for XML Path('')
   ), 1, 1, ''),1,500) as nvarchar(500)) as "FIRSTNAMES",*/**

         per."fnam" + '-' + per."snam"  as "FULL NAME",  
         pr."addr" + ' '+ pr."postcode"     as  "FULL ADDRESS" ,
         prh."tenu-cde",
  prh."per-num"                 as  "PERSON NUMBER",
  prh."lead-tenant"        as  "LEAD TENANT"   ,
From     TNCY   t, 
         PR     pr,   
         PRHST  prh,
         per    per  
Where    t."prhst-occ-num"=prh."occ-num"
  and    pr."seq-no"=prh."pr-seq-no"
  and    per."num"=prh."per-num"
order by 1,2

Your quick response will be appreciated.

Thanks

A: 

So you'd like to squash multiple rows into one text column. The for xml clause can do that, but you usually apply it to a subquery. For example:

select  TextColumn + ';' as [text()]
from    (
        select FirstName + ' ' + LastName as TextColumn
        from Customers
        ) sub
for xml path('')

If you input a table with names, this query prints one row:

Jeffrey Jones;Andrei Voronkov;George Boole;Kurt Gödel

You can replace the subquery with any other query, for example:

select  TextColumn + ';' as [text()]
from    (
        select  t.[prhst-occ-num] + ' ' + per.fnam + '-' + 
                    per.snam as TextColumn
        from    TNCY   t, 
                PR     pr,   
                .....
        order by
                t.[prhst-occ-num]
        ) sub
for xml path('')
Andomar
Hi,I have removed the order by but getting the following errorMsg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value ';' to data type int.Thanks
Use `cast(col1 as varchar(25))` before concatenating a number onto a string. For example, `cast(t."prhst-occ-num" as varchar(10)) + ' ' + per."fnam"`
Andomar
A: 

Hi,

Thanks for your prompt repky, I'm getting the following message and want to clarify the scenario.

if in a property a couple lives so instead of showing them in two rows i want to show them as one (by combining the first name & surname)

Msg 1033, Level 15, State 1, Line 21 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Select   distinct(t."prhst-occ-num") as "TENANCY_REF",         
         per."pr-seq-no"   as "PROPERTY-REF-NO" ,         
         pr."user-cde"     as "USER_CDE",         
            /*CAST( SUBSTRING( stuff ((
            select distinct(',' + per."fnam")
            From   per per ,
                prhst  prh,
                aTNCY   t 
            where  prh."per-num"=per."num"
            and  t."prhst-occ-num"=prh."occ-num"  
            and  prh."lead-tenant" in (0,1)
            for XML Path('')
            ), 1, 1, ''),1,500) as nvarchar(500)) as "FIRSTNAMES",*/
            (select  TextColumn + ';' as [text()]
             from (select  t."prhst-occ-num" + ' ' + per."fnam" + '-' + "per.snam" as TextColumn
                   From    TNCY   t, 
                           PR     pr   
                   Where  prh."per-num"=per."num"
                        and  t."prhst-occ-num"=prh."occ-num"  
                        and  prh."lead-tenant" in (0,1)
                   Order by t."prhst-occ-num") sub
             for xml path('') ,
         --per."fnam" + '-' + per."snam"  as "FULL NAME",  
         pr."addr" + ' '+ pr."postcode"     as  "FULL ADDRESS" ,
         CASE
            WHEN isnull(t."term-dat",getdate()+1) > getdate()
            THEN 'C'
            ELSE 'F'
         END current_or_former,
         prh."tenu-cde",
         prh."per-num"                 as  "PERSON NUMBER",
         prh."lead-tenant"         as  "LEAD TENANT"   ,
         (select distinct(per."Day-tel-no")         
            From   "per" per,
                   "PRHST" prh
            Where  per."num"=prh."per-num"
              and  prh."lead-tenant"='1') as "Day-Tel-No",
         (select distinct(per."Mobile-no")      
            From   "per" per,
                   "PRHST" prh
            Where  per."num"=prh."per-num"
              and  prh."lead-tenant"='1') as "Mobile-No",
         (select distinct(per."e-mail") 
            From   "per" per,
                   "PRHST" prh
            Where  per."num"=prh."per-num"
              and  prh."lead-tenant"='1') as "Email",
         (Select distinct CASE 
           When prh."tenu-cde" in('CT','TA','AS','CV')  THEN 'General Needs'
           when prh."tenu-cde" in('SH')  THEN 'Shared Ownership' 
           --when dwelling_type code <> 'W'
           --when dwelling_type code <> 'W' 
           --when dwelling_type code <> 'W'         
           Else 'LeaseHolder' 
           End
          From  
            PRHST  prh          
          Where      t."prhst-occ-num"=prh."occ-num") as "Management Area"

From     TNCY   t, 
         PR     pr,   
         PRHST  prh,
         per    per  
Where    t."prhst-occ-num"=prh."occ-num"
  and    pr."seq-no"=prh."pr-seq-no"
  and    per."num"=prh."per-num"
order by 1,2
When posting, you can use the button with 10101 to format a query as code. Instead of posting an answer to your own question, edit the original question to include new information.
Andomar
To answer this question: move the order by from the subquery to the line after `for xml path`. This might mean you have to include extra columns in the subquery, or you can just `order by TextColumn`.
Andomar
A: 

Hi, its working now but its taking ages and the output is like this although it should return one row for each teant

Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname-lastname;Firstname- Thanks

Maybe post a new question with your current query, example output, and what you would like the output to be?
Andomar
Hi,I want the output to be like this123 1234 12345 MR Rose ; MRS ROSE Temp add 1 ; 0 instead of having the following outputTenancy No Property NO usercode name Address person no lead tenant123 1234 12345 MR Rose Temp add 1 1 123 1234 12345 MRS Rose Temp add 2 0