Pivot table

[abenitez77]

How can i get my table to show the results i want below? Pivot table? I've never used pivot before.

Table:
tocid prop_id str_val num_val
1059786 104 USD NULL
1059786 105 Pricing Null
1059786 108 NULL 1539.17


results:
tocid str_val str_val num_val
1059786 USD Pricing 1539.17

 

[gmmastros]

Here is an example that will work with any version of SQL Server.

I first create a table variable with some sample data, and then I show the query. You can copy/paste the code below in to a query window and run it. If you are satisfied that it returns the correct results, then you can remove the table variable and change the FROM clause to use your real table.

Declare @Temp Table(tocid int, prop_id Int, str_val VarCHar(20), num_val Decimal(10,2))

Insert Into @Temp Values(1059786,104,'USD'    ,NULL)
Insert Into @Temp Values(1059786,105,'Pricing',Null)
Insert Into @Temp Values(1059786,108,NULL     ,1539.17)

Select tocid,
       Min(Case When Prop_ID = 104 Then str_val End) As A,
       Min(Case When Prop_ID = 105 Then str_val End) As B,
       Min(Case When Prop_ID = 108 Then num_val End) As C
From   @Temp
Group By tocid

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[abenitez77]

I have a table that contains the structure values. This is a sample of the table...it has about 500 rows. How can i use this with my query?

prop_id prop_name prop_type
13 Region L
14 Client Number L
15 Client Name L
16 Audit Year L
17 Audit Number L
18 Vendor Rank I
19 Vendor Number S
20 Vendor Name S
21 Document Type L
22 Owner L
29 Document Security L
30 Auditor ID S
31 Audit Type L
32 Client Division S
33 GST N
34 HST N
35 QST N
38 Cancel Reason L
39 Language S
40 Creator S
41 Mail Code L
42 CY Date D