SQL select statement if got 5 result, how to separate each record with ' ; ' (Semi-Colon) possible?

SQL STATEMENT HOW TO SEPARATE RESULT WITH ' ; '.

How to get exact two columns of table(s) from a entair database in SQL server

There are two tables - Entity table and Account table. Entity table having EntityID and Account table having AccountID. There is a 3rd table EntityAccountAssociation which contains both EntityID and AccountID. - This I know.

My scenario is; suppose in a product support, I do not know about this 3rd table (and no one is there to tell me) , then, is there any query to find out this 3rd table to get the relationship in a huge database with 100s of table?

For Example: In that query, I will pass these two column name as parameter   and it will show me on which particular table these two columns exists.

Please help and let me know. Thanks.

Inserting extra rows into a result set based on the data length of a text column

Because of a bug in my reporting tool (Rave reports) which causes errors with text columns that contain more than X characters i need to break some rows in a result set into extra rows. Say I have a table called transactions like:

CREATE TABLE Trans
    (id int, type int, description varchar(55), memo text)
;

INSERT INTO Trans
    (id, type, description, memo)
VALUES
    (1, 1,  'blah', 'hi there'),
    (2, 100, 'foobar', 'yawn'),
    (3, 700, 'emailmessage', 'This some long text that needs to broken into chunks. This some long text that needs to broken into chunks. This some long text that needs to broken into chunks. '),
    (4, 1,   'blah blah blah', 'some other text')
;

Look at the third row, it has approx 160 chars, and lets say I want to break row 3 into 4 extra rows each with a chunk of no more than 50 chars each of the long memo. The final result of the query would be the same structure as the Trans table but now with 7 rows. The ID column should be renumbered to reflect the extra rows. I need it for SQL Server 2005.

Of course the extra rows would each have a different chunk of the long memo in the correct order but their other column values would be copies of the values in the original row

I know I could use a cursor to do it but I'm looking for better way.

The result set for this example and a chunk size of 50 chars should look like this:

id    type  description     memo
----- ----- --------------- -------------------------------------------------
1     1     blah            hi there
2     100   foobar          foobar
3     700   emailmessage    This some long text that needs to broken into chu
4     700   emailmessage    nks. This some long text that needs to broken int
5     700   emailmessage    o chunks. This some long text that needs to broke
6     700   emailmessage    n into chunks.
7     1     blah blah blah  some other text

Make the chunk length a variable that I can alter. Thanks in advance.

Copying an SQL table from one Server to another on SQL Server 2000 / 2005

I’m trying to copy a SQL Server table, schema and data, from Server A to Server B. The SQL Server table is just a reference table which hasn't been populated for some reason on Server B. Can anyone advise how the entire table could be copied across please? On SQL Server 2000/2005.

So far we've tried a long-winded approach by copying the .mdf and .ldf files from Server A to Server B with a plan to then copy the table across into the Server B database but we are having some difficulty re-attaching the database to Server B.

Please can anyone help?

Kind Regards James

To repeat header on each page of SSRS 2005 On Matrix- RepeatOnNewPage Not support on ssrs 2005

I would like to repeat for each page the header of the matrix but can not find solution ...

all recommend using RepeatOnNewPage but in ssrs 2005 is not supported see

xsd http://ift.tt/1NHayj7

get last 3 month on year in sql server

I want to get last 3 months name from current month. For example current month is December. So, I want get like this October, November and December.

this is my query

SELECT CONVERT(CHAR, DATENAME(MONTH, IssueDate)) AS MonthName, ItemId
FROM dbo.Issue AS Issue
GROUP BY CONVERT(CHAR, DATENAME(MONTH, IssueDate)), ItemId
HAVING (ItemId = 427)

Now show this

But, my need is - required this

N.B. When December month close and January month open then October auto excluded as like (November, December and January)

How to use two pivot

I have 3 Tables 1) user 2) phone 3) address

1) user

 id    name
 1     abc
 1     abc
 2      tyu
 2      tyu

2) phone

    id     number
     1     0987654
     1     0890764
     2     3445667
     2     5643456

3) address

id     addr  type
 1    usa    1
 1    uae    1 
 2    Uk     2

So now i have written below query:

select * from (
              select u.id, u.name , p.number, cs.COL + CAST(row_number()over(PARTITION BY u.ID ORDER BY cs.COL) AS VARCHAR) RN ,
              cs1.COL + CAST(row_number()over(PARTITION BY a.IDnum ORDER BY cs1.COL) AS VARCHAR) RN1
               ,a.addr1
              from user u left join phone p on p.id = u.id
              left join address as a on a.id = p.id CROSS APPLY (VALUES ('phone',number)) CS(Col,val)
                                                          CROSS APPLY (VALUES ('add',a.addr)) CS1(Col,val)
              where u.id=1 and a.type = '1'
              )P
    PIVOT (MAX(number) FOR RN IN ([phone1],[phone2])) as pivot1
    PIVOT (MAX(addr1)  FOR RN1 IN ([add1],[add2])) as pivot2

so the above query give me output like:

id  name    phone1      phone2      add1        add2
1   abc     NULL        0987654     NULL       usa
1   abc     0890764      NULL       uae         NULL   

But i want the ouput like below:

  id    name    phone1      phone2      add1        add2
    1   abc     0890764     0987654     uae         usa

So how can i achieve this with the pivot ?