|
Home > Archive > MS SQL Server > October 2006 > Outputting data in a single row
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Outputting data in a single row
|
|
| andy.williams1971@ntlworld.com 2006-10-24, 6:28 pm |
| I'm using SQL Server 2000
I currently have code (outputting to .csv) that selects Numbers 1 and 2
that outputs data to multiple rows. The amount of rows returned is
variable depending on the amount of Numbers 1 and 2 that is found, for
example:
UniqueIdentifer Number 1 Number 2
0001 1234 5678
0001 1234 6543
I want to mail merge this data, so ideally I want all the data output
on a single line, for example:
UniqueIdentifer Number 1 Number 2 Number 1 Number 2
0001 1234 5678 1234 6543
Can this be done?
| |
| Uri Dimant 2006-10-24, 6:29 pm |
| Hi
Use SELF join
<andy. williams1971@ntlworl
d.com> wrote in message
news:1158588351.882488.11450@d34g2000cwd.googlegroups.com...
> I'm using SQL Server 2000
>
> I currently have code (outputting to .csv) that selects Numbers 1 and 2
> that outputs data to multiple rows. The amount of rows returned is
> variable depending on the amount of Numbers 1 and 2 that is found, for
> example:
>
> UniqueIdentifer Number 1 Number 2
> 0001 1234 5678
> 0001 1234 6543
>
> I want to mail merge this data, so ideally I want all the data output
> on a single line, for example:
>
> UniqueIdentifer Number 1 Number 2 Number 1 Number 2
> 0001 1234 5678 1234 6543
>
>
> Can this be done?
>
| |
| andy.williams1971@ntlworld.com 2006-10-24, 6:29 pm |
| As I'm getting all the variable data from a single table I can't see
how SELF Join will work? Am I missing something?
Uri Dimant wrote:[color=darkred
]
> Hi
> Use SELF join
>
>
> <andy. williams1971@ntlworl
d.com> wrote in message
> news:1158588351.882488.11450@d34g2000cwd.googlegroups.com...
| |
| Uri Dimant 2006-10-24, 6:29 pm |
| Hi
CREATE TABLE #Test (
UniqueIdentifer VARCHAR(50),
Number1 INT,
Number2 INT
)
INSERT INTO #Test VALUES ('0001',1234,5678)
INSERT INTO #Test VALUES ('0001',1234,6543)
SELECT #Test. UniqueIdentifer,#Tes
t. Number1,MIN(Number2)
AS '2'
,Der.Number1,N
FROM #Test JOIN (SELECT UniqueIdentifer,Numb
er1,MAX(Number2)as n
FROM #Test GROUP BY UniqueIdentifer,Numb
er1)AS Der
ON #Test.UniqueIdentifer=Der.UniqueIdentifer
GROUP BY #Test. UniqueIdentifer,#Tes
t.Number1,Der.Number1,N
<andy. williams1971@ntlworl
d.com> wrote in message
news:1158672863.607715.196650@d34g2000cwd.googlegroups.com...
> As I'm getting all the variable data from a single table I can't see
> how SELF Join will work? Am I missing something?
>
>
> Uri Dimant wrote:
>
|
|
|
|
|