Combine data from one column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine data from one column

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-17 : 05:42:11

Hi there,

I have one database, with 3 different tables: A, B and C
(All tables have the same columns).

In table A, Column_stock, I have: Red, Blue and Green
In table A, Column_date, I have: Last_information_date
In table A, Column_ID, I have: ID

I need to create a new_table that using the information of both tree columns, give me as an output:

Using ID column, give me:

The Last_information_date for stock_red
The Last_information_date for stock_Blue
The Last_information_date for stock_green

So I will have all the ID's sorted by the column_Stock (red, blue and green) using the last_info_date of each one.

I have used the following query:

Select * from File_A, File_B and File_C
where File_A.ID = File_B.ID
and File_B.ID = File_C.ID

Apparently is working, but I cant create another table with all this info, because:

msg 2705, level 16, state 3, line 2
column names in each table must be unique.

Any idea? thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 06:30:06

To get rid of the complaint about non-uniqueness of column names, assign aliases to the columns, at least to those that are in both tables such as ID. Something like this:

SELECT File_A.Col1 AS ACol1,
       File_A.Col1 AS ACol2,
       ...
       File_B.Col1 AS BCol1,
       File_C.Col2 AS BCol2,
       ....
FROM   File_A,
       File_B,
       File_C
WHERE  File_A.ID = File_B.ID
       AND File_B.ID = File_C.ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:02:29

sounds like PIVOT to me


SELECT ID,[1],[2],[3]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Last_information_date DESC) AS Rn,*
FROM table)t
PIVOT(MAX(Last_information_date) FOR Rn IN ([1],[2],[3]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources