0

Add column To select Table by select all items from another table in sql Server. I have Tow Table Like this:

Table1

ID        ||         Title
1         ||         Ruler
2         ||         Book
3         ||         Pen
.         ||         .
.         ||         .
.         ||         .

Table2

itemID    ||         Price     ||         Date
1         ||         200       ||         2016-01-21
2         ||         30        ||         2017-03-01
3         ||         27        ||         2014-06-09
.         ||         .
.         ||         .
.         ||         .

Table Result

      Date         ||       Ruler      ||      Book       ||      pen         ||         … more 
2016-01-21         ||       200        ||                 ||                  ||         
2017-03-01         ||                  ||         30      ||                  ||         
2014-06-09         ||                  ||                 ||       27         ||         
.
.
.

It doesn't work

Declare @cols1 varchar(max)
Declare @query nvarchar(max)
Select @cols1 = stuff((select distinct ','+QuoteName([Title]) from table1 for xml path('')),1,1,'')
Set @Query = ' Select * from (


Select t2.[Date], t1.[Title], t2.Price from table2 t2 inner join table1 t1



on t2.ItemId = t1.Id ) a pivot (max([Price]) for [Title] in ( ' +@cols1 + ' ) ) p '
exec sp_executeSql @query

its return Max Price, but I Want Last Price Like this:

pivot (Select ([Price]) from table2 order by Date desc for [Title] in ( ' +@cols1 + ' ) ) p '

syntax Error return !?

abdol-hamid Hosseiny
  • 122
  • 10
  • Where did you get the syntax `Table1.ID.*`? Try removing the `.*` from your current query. – Tab Alleman Jun 22 '17 at 15:40
  • 1
    Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Tab Alleman Jun 22 '17 at 15:41

1 Answers1

0

you can use pivot as below:

Select * from (
   Select t2.[Date], t1.[Title], t2.Price from table2 t2 inner join table1 t1
   on t2.ItemId = t1.Id ) a
pivot (max([Price]) for [Title] in ([Ruler],[Book],[Pen]) ) p

For dynamic list of title's you can query as below:

Declare @cols1 varchar(max)
Declare @query nvarchar(max)

Select @cols1 = stuff((select distinct ','+QuoteName([Title]) from table1 for xml path('')),1,1,'')

Set @Query = ' Select * from (
   Select t2.[Date], t1.[Title], t2.Price from table2 t2 inner join table1 t1
   on t2.ItemId = t1.Id ) a
pivot (max([Price]) for [Title] in ( ' +@cols1 + ' ) ) p '

exec sp_executeSql @query
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38