Sunday, March 11, 2012

Another query help

I have a table with the following data.
col1 col2
DB1 tabl1
DB1 tabl2
DB1 tabl3
How can I get the following query result ? Only one DB1 and all the
belonging tables.
DB1 tabl1
tabl2
tabl3
Thanks.This is not possible. This is not how SQL Server works. What you are
talking about here is at the presentation level. SQL Server does not
concern itself with this level. You need to do something like that
with, say, Access reports.
Jonathan
DXC wrote:
> I have a table with the following data.
> col1 col2
> DB1 tabl1
> DB1 tabl2
> DB1 tabl3
> How can I get the following query result ? Only one DB1 and all the
> belonging tables.
> DB1 tabl1
> tabl2
> tabl3
> Thanks.|||On Sep 6, 2:42 pm, DXC <D...@.discussions.microsoft.com> wrote:
> I have a table with the following data.
> col1 col2
> DB1 tabl1
> DB1 tabl2
> DB1 tabl3
> How can I get the following query result ? Only one DB1 and all the
> belonging tables.
> DB1 tabl1
> tabl2
> tabl3
> Thanks.
CREATE TABLE #t(col1 VARCHAR(5), col2 VARCHAR(5))
INSERT #T VALUES('DB1','tabl1')
INSERT #T VALUES('DB1','tabl2')
INSERT #T VALUES('DB1','tabl3')
INSERT #T VALUES('DB2','tabl1')
INSERT #T VALUES('DB3','tabl2')
INSERT #T VALUES('DB3','tabl3')
SELECT CASE WHEN rn=1 THEN col1 ELSE '' END, col2 FROM(
SELECT col1, col2, ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2)
rn
FROM #t
) t
ORDER BY col1, col2
col2
-- --
DB1 tabl1
tabl2
tabl3
DB2 tabl1
DB3 tabl2
tabl3|||Thanks bu I think ROW_NUMBER, PARTITION are functions in later SQL Server
versions. I am using this on SQL Server 7.0.
Thanks again......
"Alex Kuznetsov" wrote:
> On Sep 6, 2:42 pm, DXC <D...@.discussions.microsoft.com> wrote:
> > I have a table with the following data.
> >
> > col1 col2
> > DB1 tabl1
> > DB1 tabl2
> > DB1 tabl3
> >
> > How can I get the following query result ? Only one DB1 and all the
> > belonging tables.
> >
> > DB1 tabl1
> > tabl2
> > tabl3
> >
> > Thanks.
> CREATE TABLE #t(col1 VARCHAR(5), col2 VARCHAR(5))
> INSERT #T VALUES('DB1','tabl1')
> INSERT #T VALUES('DB1','tabl2')
> INSERT #T VALUES('DB1','tabl3')
> INSERT #T VALUES('DB2','tabl1')
> INSERT #T VALUES('DB3','tabl2')
> INSERT #T VALUES('DB3','tabl3')
> SELECT CASE WHEN rn=1 THEN col1 ELSE '' END, col2 FROM(
> SELECT col1, col2, ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2)
> rn
> FROM #t
> ) t
> ORDER BY col1, col2
> col2
> -- --
> DB1 tabl1
> tabl2
> tabl3
> DB2 tabl1
> DB3 tabl2
> tabl3
>

No comments:

Post a Comment