SQL Server

[madhurjya]

can any one help me to solve the following problem.
i have a table name "detail" and field contains name,age,remarks1,remarks2

and data content

name age remark1 remark2
bishnu 22 aaa ssss
priya 27 ff gg
gopal 12 tt ww

now i want to generate a new table name "complete" which generate the following table as from table name "detail"

name age remark
bishnu 22 aaa
bishnu 22 ssss
priya 27 ff
priya 27 gg
gopal 12 tt
gopal 12 ww

please tell me the sql coding for solving this problem.plz help

[debasisdas]

As you have posted a question in the SQL Server Articles section it is being moved to SQL Server Forum.

[Jim Doherty]

can any one help me to solve the following problem.
i have a table name "detail" and field contains name,age,remarks1,remarks2

and data content

name age remark1 remark2
bishnu 22 aaa ssss
priya 27 ff gg
gopal 12 tt ww

now i want to generate a new table name "complete" which generate the following table as from table name "detail"

name age remark
bishnu 22 aaa
bishnu 22 ssss
priya 27 ff
priya 27 gg
gopal 12 tt
gopal 12 ww

please tell me the sql coding for solving this problem.plz help

If you merely want to bring in remarks2 column so that it becomes remarks1 column then a SELECT INTO statement will automatically create a table for you called 'content' defined according to the UNION of Two SELECT statements of data retrieved from the source table 'detail' and in so doing inherit the datatypes of the source table in the process as follows:

 
SELECT     TOP 100 PERCENT name, age, remark1
INTO            content
FROM         (SELECT     name, age, remark1
                     FROM         dbo.detail
                     UNION ALL
                     SELECT     name, age, remark2
                     FROM         dbo.detail) DERIVEDTABLE
ORDER BY name
 

You can run this SQL either in query analyser of in the 'View' designer window (although in the latter whilst you will be prompted by the designer that it does not support the SQL statement type in a view it will still run it as valid syntax and thus create your table and populate it accordingly)

if your strict need is to have the field name of 'Remark' as a replacement in effect for the Remark1 field in the newly created table then you can alias the name as bold highlighted in the following repeat of the above SQL at which point the new table 'content' will reflect that field name.

 
SELECT     TOP 100 PERCENT name, age, remark1 AS Remark
INTO            content
FROM         (SELECT     name, age, remark1
                     FROM         dbo.detail
                     UNION ALL
                     SELECT     name, age, remark2
                     FROM         dbo.detail) DERIVEDTABLE
ORDER BY name
 

Research the UNION operator in SQL to understand how data is treated using this particular method. In your specific example data case there is no duplication of data, but in a real data sense if there were duplications caused by the UNION then removing the ALL word (highlighted above) would cause the dataset to be created and remove any duplications automatically.

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets combined using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types.

Hope this helps you.

Regards

Jim :)