Pivot operation help required

• chandrakant_gaurav

  Right there with Babe

  Points: 799

  More actions

  July 14, 2010 at 5:24 am

  #224843

  Hi all

  I have a requirement while using SQL server 2005, where two tables are involved:

  ImageData having (ImageID, ImangeName, tagid1,tagid2,tagid3,tagid4) as columns , and,

  Imagetag having (Tagid, tagname)

  An image can have multiple tags.

  I need to have the TagIds of particular imageid in one row.

  How can we use pivot operator to do this?

  Please clarify.

  thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 14, 2010 at 5:28 am

  #1192679

  here's a really nice example of using PIVOT that i picked up from a post here.

  if you were able to provide the two table definitions and some sample data, we could help adapt the example to the details you provide.

  this example expects eactly 4 items to pivot, but is easily changable.

  if the # items is dynamic, you'll wan tto search for "dynamic pivot" and read the article for that instead.

  CREATE TABLE product (ProductID INT , ProductName VARCHAR(255))

  INSERT INTO product values (1,'First Product')

  go

  CREATE TABLE product_items (productID INT , ItemID INT )

  INSERT INTO product_items values (1, 10)

  INSERT INTO product_items values (1, 20)

  INSERT INTO product_items values (1, 30)

  INSERT INTO product_items values (1, 40)

  go

  ;with cte as

  (

  select productID ,ItemID,

  row_number() over (partition by productID order by ItemID desc) RN

  from product_items

  )

  --select * from

  select productID,[1] as Val1,[2] as Val2,[3] as Val3,[4] as Val4 from

  ( select * from cte where RN <= 4 ) pivot_handle

  pivot

  (MAX(ItemID) for RN in ([1],[2],[3],[4])) pivot_table

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• davidandrews13

  SSCertifiable

  Points: 6820

  More actions

  July 14, 2010 at 5:46 am

  #1192699

  this is a thread that gave me alot of help on this subject.

  http://www.sqlservercentral.com/Forums/Topic747597-392-1.aspx