Change the table structure

Hi...

I execute select statement on three tables and i get the following table:

ID

Value

Name

1

10

color

2

20

color

3

30

color

4

40

color

and, from this table i want to create the following table:

ID

color

1

10

2

20

3

30

4

40

mean, the value color become the header title of the column.

How can i do that?

thank you...

But you use a GridView or what to show the table ?

If you use a GridView you can set the Visible attribute of a BoundField at runtime.

|||

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

|||

may215:

Hi...

I am not using gridView, or any other control, i just need to do this in sql.

thanks.

Are you going to insert values in the second table, or its value will be same as the first table but columns' names are diffrents?

If you are not going to insert values in the second table, then I suggest to create a view that get its data from the first table.

1CREATE VIEW MyViewForTheSecondTable23AS45SELECT [ID], [VALUE]AS'Color'67FROM MyFirstTable8910

Now, you can do the SELECT on the view like:

1SELECT *FROM MyViewForTheSecondTable

The returned result will be same as in the first table but columns name will be ID and Color insted of ID and Value.

Good luck.

|||

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

|||

may215:

Hi...

When i try to create the view i am getting error: invalid column name ID, VALUE....i

I cheked the example I wrote .. it is working fine.

Here is a complete senario that worked on my machine and it should work on your machine as well:

1-- create sample database2create database MyTestDB3go45-- use the sample database6use MyTestDB7go89-- create new table for testing10CREATE TABLE MyTable11(12[ID]INT,13[VALUE]CHAR(10)14)15GO1617-- insert two records in the testing table18INSERT INTO MyTableSELECT 4,'red'19INSERT INTO MyTableSELECT 5,'black'202122-- create a view23CREATE VIEW MyViewForTheSecondTable2425AS2627 SELECT [ID], [VALUE]AS'COLOR'2829FROM MyTable303132-- select data from the created view33SELECT *FROM MyViewForTheSecondTable

Please let me know if it did not work with you for any reason.

may215:

in the following example: invalid column name PhonePropertyValue , PhonePropertyName.

here is the full example i trying to do:

SELECT [dbo.TblPropertyForPhoneType.PhonePropertyValue], [dbo.TblPhoneProperty.PhonePropertyName] AS
'color'

FROM dbo.TblPhoneType INNER JOIN
dbo.TblPropertyForPhoneType ON dbo.TblPhoneType.PhoneTypeID = dbo.TblPropertyForPhoneType.PhoneTypeID INNER JOIN
dbo.TblPhoneProperty ON dbo.TblPropertyForPhoneType.PhonePropertyID = dbo.TblPhoneProperty.PhonePropertyID
WHERE (dbo.TblPhoneProperty.PhonePropertyID = '34')

Why is that?

thanks...

First of all, please make sure those columns are exists in the table and they were typed correctly.

Then try this:

1SELECT prop.PhonePropertyValue, prop.PhonePropertyNameAS'color'23FROM dbo.TblPhoneType pt4INNERJOIN5 dbo.TblPropertyForPhoneType prop6ON pt.PhoneTypeID = prop.PhoneTypeID7INNERJOIN8 dbo.TblPhoneProperty pp9ON pp.PhonePropertyID = prop.PhonePropertyID10WHERE pp.PhonePropertyID ='34'11

Good luck.