Prev: Need help with this query
Next: Invalid Class String
From: Stephane on 4 Jul 2010 05:30
Hi

i have a table table1 in sql server database with 3 columns and 3 Rows like

Column1 Column2 Column3
Data 11 Data 12 Data 13
Data 21 Data 22 Data 23
Data 31 Data31 Data 33

i would like to create a view view1 based on table1 which retrieve data like

Column1 Column2 Column3 Column1
Column2 Column3 Column1 Column2
Column3
Data 11 Data 12 Data 13 Data 21
Data 22 Data 23 Data 21 Data 22
Data 23

in others words i would like to create a view which returns only one row for
all data in the table, and each data must match with its column


is there any keyword in transact sql or an other way to do that easily
without create 3 views for those 3 rows and doing cross join of those views?

any help is welcome

thank in advance

sorry for my poor english.




From: John Bell on 4 Jul 2010 06:40
On Sun, 4 Jul 2010 11:30:08 +0200, "Stephane" <ntsteph(a)yahoo.fr>
wrote:

>Hi
>
>i have a table table1 in sql server database with 3 columns and 3 Rows like
>
>Column1 Column2 Column3
>Data 11 Data 12 Data 13
>Data 21 Data 22 Data 23
>Data 31 Data31 Data 33
>
>i would like to create a view view1 based on table1 which retrieve data like
>
>Column1 Column2 Column3 Column1
>Column2 Column3 Column1 Column2
>Column3
>Data 11 Data 12 Data 13 Data 21
>Data 22 Data 23 Data 21 Data 22
>Data 23
>
>in others words i would like to create a view which returns only one row for
>all data in the table, and each data must match with its column
>
>
>is there any keyword in transact sql or an other way to do that easily
>without create 3 views for those 3 rows and doing cross join of those views?
>
>any help is welcome
>
>thank in advance
>
>sorry for my poor english.
>

Hi

It is always best to post your DDL for the CREATE TABLE and example
data as insert statements to give full idea of what you require. You
are probably looking for to PIVOT the data, but if you only have three
rows and columns then you may as well just join the tables. This
assume you are on a version of SQL Server that has CTEs and
ROW_NUMBER.


CREATE TABLE tbl1 ( column1 int, column2 int, column3 int )

INSERT INTO tbl1 ( column1, column2, column3 )
SELECT 1,2,3
UNION ALL SELECT 4, 5, 6
UNION ALL SELECT 7, 8, 9


WITH CTE ( column1, column2, column3, rownum )
AS
(
SELECT column1, column2, column3, ROW_NUMBER() OVER ( ORDER BY
column1 ) as rownum
FROM tbl1
)
SELECT a.column1, a.column2, a.column3
,b.column1, b.column2, b.column3
,c.column1, c.column2, c.column3
FROM CTE a
join CTE b on b.rownum = a.rownum + 1
join CTE C on c.rownum = a.rownum + 2
WHERE a.rownum = 1 ;

John
From: Stephane on 4 Jul 2010 17:12
Thanks

"John Bell" <jbellnewsposts(a)hotmail.com> a �crit dans le message de groupe
de discussion : a3p036hd79i98envd14tl9sjpo8ticpnvu(a)4ax.com...
> On Sun, 4 Jul 2010 11:30:08 +0200, "Stephane" <ntsteph(a)yahoo.fr>
> wrote:
>
>>Hi
>>
>>i have a table table1 in sql server database with 3 columns and 3 Rows
>>like
>>
>>Column1 Column2 Column3
>>Data 11 Data 12 Data 13
>>Data 21 Data 22 Data 23
>>Data 31 Data31 Data 33
>>
>>i would like to create a view view1 based on table1 which retrieve data
>>like
>>
>>Column1 Column2 Column3 Column1
>>Column2 Column3 Column1 Column2
>>Column3
>>Data 11 Data 12 Data 13 Data 21
>>Data 22 Data 23 Data 21 Data 22
>>Data 23
>>
>>in others words i would like to create a view which returns only one row
>>for
>>all data in the table, and each data must match with its column
>>
>>
>>is there any keyword in transact sql or an other way to do that easily
>>without create 3 views for those 3 rows and doing cross join of those
>>views?
>>
>>any help is welcome
>>
>>thank in advance
>>
>>sorry for my poor english.
>>
>
> Hi
>
> It is always best to post your DDL for the CREATE TABLE and example
> data as insert statements to give full idea of what you require. You
> are probably looking for to PIVOT the data, but if you only have three
> rows and columns then you may as well just join the tables. This
> assume you are on a version of SQL Server that has CTEs and
> ROW_NUMBER.
>
>
> CREATE TABLE tbl1 ( column1 int, column2 int, column3 int )
>
> INSERT INTO tbl1 ( column1, column2, column3 )
> SELECT 1,2,3
> UNION ALL SELECT 4, 5, 6
> UNION ALL SELECT 7, 8, 9
>
>
> WITH CTE ( column1, column2, column3, rownum )
> AS
> (
> SELECT column1, column2, column3, ROW_NUMBER() OVER ( ORDER BY
> column1 ) as rownum
> FROM tbl1
> )
> SELECT a.column1, a.column2, a.column3
> ,b.column1, b.column2, b.column3
> ,c.column1, c.column2, c.column3
> FROM CTE a
> join CTE b on b.rownum = a.rownum + 1
> join CTE C on c.rownum = a.rownum + 2
> WHERE a.rownum = 1 ;
>
> John

 | 
Pages: 1
Prev: Need help with this query
Next: Invalid Class String