SQL query to get columns and records of that columns using like operator

Question

Hi,

I have a table as show below the bold words are column names KEYWORD PART1_D1 PART1_D2 PART1_D3 PART1_D4 PART2_D5
y 1 0 0 0 1
Chinese 3 2 2 1 1
Tokyo 1 0 0 0 1
Japan 1 0 0 0 1
Beijing 0 1 0 0 0
Shangh 0 0 1 0 0

i wrote a query as below

Select COLumn_NAME From INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'sample' and COLumn_NAME like '%part1%' the out put is iam getting only columns as shown below

COLumn_NAME part1_d1 part1_d2 part1_d3 part1_d4

but i want to get records also from the table like my output should be as show below Keyword PART1_D1 PART1_D2 PART1_D3 PART1_D4
y 1 0 0 0
Chinese 3 2 2 1
Tokyo 1 0 0 0
Japan 1 0 0 0
Beijing 0 1 0 0
Shangh 0 0 1 0

Answer 1

if you know the column names ahead of time, just UNION in an extra record. Of course, then your resultset will have to have all character fields:

Select 'KEYWORD' as KEYWORD, 'PART1_D1' as PART1_D1, 'PART1_D2' as PART1_D2, 
  'PART1_D3' as PART1_D3, 'PART1_D4' as PART1_D4, 'PART2_D5' as PART2_D5,
UNION
Select KEYWORD, cast(PART1_D1 as varchar(50), cast(PART1_D2 as varchar(50), 
  cast(PART1_D3 as varchar(50), cast(PART1_D4 as varchar(50), 
  cast(PART2_D5 as varchar(50)
From sample