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.
| Author |
Topic |
|
Johno3535
Starting Member
1 Post |
 Posted - 2010-09-05 : 02:14:17
|
| Hi,I have 2 tables that I would like to join in a view. But the second table has a varying number of multiple records for each record in the first table.Here is a simplified version of the two tables:Table 1:ID Type Priority------- ----- -----myid1 major 1myid2 minor 2myid3 other 8Table 2:ID Keyvalue datavalue------- -------- ---------myid1 url Id1valuemyid1 user Timmymyid2 valid Truemyid3 security authorizedI would like a view that produces the following result:ID Type Priority url user valid security----- ----- -------- --- ----- ----- --------myid1 major 1 id1value Timmy null nullmyid2 minor 2 null null True nullmyid2 other 8 null null null authorizedI was hoping to do this using a pivot to make the keyvalue field become columns in the view with the data for those columns coming from the datavalue field. But it seems pivots need aggregates.Can anyone tell me the best way to accomplish what I am trying to do?Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-05 : 09:24:30
|
| MAX() and MIN() aggregate functions work on character and datetime data types. |
 |
|
|
|
|
|
Pivot on strings