Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Red face Unanswered: Help with pivot in SQL 2008

    Hi Guys.

    I've got such a query result with ~40k items:
    Code:
    Company_Id	Lp_ID	National_id_code	Lp_NationalIdCode	Contact_Name	Adres		Job_Title	Phone		Cell		Email
    String_Data	1	String_Data		1			String_Data	String_Data	String_Data	String_Data	String_Data	String_Data
    Lp_ID is a number in range 1 to 9, depends of the record.

    I'd like to move Lp_ID (only from 1 to 5) to Pivot and have a view:
    Code:
    Company_ID, National_id_code, Lp_ID=1&Contact_Name, LP_ID=1&Adres, LP_ID=1&Job_Title, LP_ID=1&Phone, LP_ID=1&Cell, LP_ID=1&Email, LP_ID=2&Contact_Name...
    Query for source data works. But when I try to make a pivot it screams errors: in clause ORDER BY, invalid value [1] (FOR Lp_ID IN...) and drives me crazy.

    My non-working pivot code with working main select for source data:
    Code:
    SELECT * FROM
    	(SELECT
    		dbo.Company.Company_Id,
    		ROW_NUMBER() over (partition by dbo.Company.Company_Id order by dbo.Contact.First_Name,dbo.Contact.Last_Name  ) as Lp_ID,
    		dbo.Company.National_id_code,
    		ROW_NUMBER() over (partition by dbo.Company.National_id_code order by dbo.Contact.First_Name,dbo.Contact.Last_Name  ) as Lp_NationalIdCode,
    		dbo.Contact.First_Name + ' ' + dbo.Contact.Last_Name as Contact_Name,
    		dbo.Contact.Address_1 as Adres,
    		dbo.Contact.Job_Title,
    		dbo.Contact.Phone,
    		dbo.Contact.Cell,
    		dbo.Contact.Email
    	FROM
    		dbo.Contact INNER JOIN
    					dbo.Company ON dbo.Contact.Company_Id = dbo.Company.Company_ID
    					INNER JOIN
    					dbo.Employee ON dbo.Contact.Account_Manager_Id = dbo.Employee.Employee_Id
    	ORDER BY dbo.Company.National_id_code
    	) AS SourceData
    PIVOT (
    	MAX(Contact_Name), MAX(Adres), MAX(Job_Title), MAX(Phone), MAX(Cell), MAX(Email)
    	FOR Lp_ID IN ([1], [2], [3], [4], [5])
    AS PivotTable
    Please help, tutorials operate only for aggregations (sum, avg), so they are useless for me

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    Mmm... look at pivot syntax in BOL...
    You can try to use usual group by clause
    For example: max(case Lp_ID when 1 then Contact_Name else null end)....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •