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  SQL Server 2000 Forums  SQL Server Development (2000)  SQL Syntax for Data Pivoting

Author  Topic  McDale Starting Member 8 Posts Posted - 2007-11-29 : 13:16:08 Hey there, I am trying to build a "ticket" dimension for a data warehouse. Each ticket can be made up of multiple different products, and numbers of these products. Currently, we have a table that has line items (products, draws) with a degenerate dimension key "TICKET_BUS_KEY" to mark items that were from the same ticket. (Similar to an order_header/order detail idea) What I need to do is create "Keys" for each unique ticket type: EG: Here is some source data TICKET_BUS_KEY / PRODUCT_KEY / DRAW_COUNT / BOARD_COUNT -------------------------------------------------------------- 1234 / 1 / 2 / 1 1234 / 2 / 2 / 1 4321 / 5 / 3 / 3 5432 / 1 / 2 / 3 5432 / 2 / 2 / 3 What I need is to be able to make a "Key" that will allow me to create a bridge table. The key needs to have these unique tickets in it. Here is how I was thinking the key would look: <product_key1>-<draw_count1>-<board_count1>|<product_key2>-<draw_count2>-<board_count2>|... The final result will be a table that sits between a product dimension and a ticket summary fact table. The table will look something like this with the above data and key format: COMPOSITION_KEY / PRODUCT_KEY / DRAW_COUNT / BOARD_CNT ----------------------------------------------------------------------- 1-2-1|2-2-1 / 1 / 2 / 1 1-2-1|2-2-1 / 2 / 2 / 1 5-3-3 / 5 / 3 / 3 1-2-3|2-2-3 / 1 / 2 / 3 1-2-3|2-2-3 / 2 / 2 / 3 The composition_key would then sit in the fact table, and the product_key to the dimension table. Can some one help me out with how I can pivot these rows to a single column like this? Would it be easier to pivot all into one row first (there are max 5 products per ticket) and then do it? I am unsure how to pivot rows to columns. Please help! Mike