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 |
|
keen1
Starting Member
21 Posts |
 Posted - 2004-03-09 : 07:50:58
|
| There are two tables, one of which is the master 'sales orders' table and the other is for the details of a specific sales order.SalesOrders ( ID is the primary key ):ID,SAP_No,SAP_ItemNo,SAP_ScheduleLineNo,CustomerIDSOAimChars ( SalesOrderID and CharID creates a composite PK ):SalesOrderID,CharID,Valuei want to create a report which gives one row for every sales order listing the 4 fields of the SalesOrders table and one field for every characteristic ( represented by CharID ) in the SOAimChars table.How can i manage this? |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
|
|
keen1
Starting Member
21 Posts |
Posted - 2004-03-09 : 08:08:47
|
| To give an example:SalesOrderID CharID Value------------ ------ -----1 1 121 2 1111 8 12.32 1 112 2 88.23 1 123 2 1113 8 12.33 12 0 3 13 0So, number of rows in the SOAimChars table depends on the number of Sales orders active on the system and the number of characteristics defined for the individual sales orders. |
 |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-03-09 : 09:05:35
|
| Do a forum search for Dynamic Cross-tab, unless you know the full domain of values for CharID and want to write all of the CASE WHEN statements.SELECT <fields from first table>,MAX(CASE WHEN CharID = <some value> CharID ELSE NULL END) <'FieldName'>,...FROM <tables joined>WHERE <conditions>GROUP BY <fields from first table> |
|
|
|
|
|
|
join question