how to insert row values in column

• HakunaMatata

  SSCrazy

  Points: 2610

  More actions

  January 21, 2011 at 12:24 pm

  #144812

  Hi,

  I have come across a case where I have to put all values in single row into a single column using ssis. Does anyone knows any way to achieve this?

  for eg.

  input from spreadsheet

  Col1 Col2 Col3

  A B C

  Output to sql should be like

  ColNew

  A

  B

  C

  :rolleyes:

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 21, 2011 at 12:39 pm

  #1276736

  select Col1

  from MyTable

  union all

  select Col2

  from MyTable

  Union statements will do that very easily. There are more complex methods, but this one is easiest.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• HakunaMatata

  SSCrazy

  Points: 2610

  More actions

  January 21, 2011 at 12:53 pm

  #1276746

  Thanks GSquared...

  but on excel data provider this type of query does not work.. ! It says it requires a parameter but none is provided.

  :rolleyes:

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 21, 2011 at 2:11 pm

  #1276799

  Are you using something like OpenRowset to query Excel? Or a linked server? How are you doing it?

  You should be able to use a derived table, or CTE, to query the external dataset, and then do the Union All trick in the outer query.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon