How to convert rows into columns based on the specific row value?

Your question is not exactly clear but it seems like you want to unpivot the existing columns and then pivot the values in the user_type column.

If that is the case, then you will want to use a UNION ALL to unpivot the data and then apply an aggregate function with a CASE expression to pivot to get the final result:

select user_id, 
  user_group,
  col_name,
  max(case when user_type = 'Husband' then value end) as Husband,
  max(case when user_type = 'wife' then value end) as Wife,
  max(case when user_type = 'kid1' then value end) as Kid1,
  max(case when user_type = 'kid2' then value end) as Kid2,
  max(case when user_type = 'kid3' then value end) as Kid3,
  max(case when user_type = 'ki4=d4' then value end) as Kid4
from
(
  select user_id, user_type, user_group,
      'prefix' as col_name, prefix as value
  from yourtable
  union all
  select user_id, user_type, user_group,
      'fname' as col_name, fname as value
  from yourtable
  union all
  select user_id, user_type, user_group,
      'mname' as col_name, mname as value
  from yourtable
  union all
  select user_id, user_type, user_group,
      'lname' as col_name, lname as value
  from yourtable
  union all
  select user_id, user_type, user_group,
      'suffix' as col_name, suffix as value
  from yourtable
  union all
  select user_id, user_type, user_group,
      'nick_name' as col_name, nick_name as value
  from yourtable
  union all
  select user_id, user_type, user_group,
      'company' as col_name, company as value
  from yourtable
  union all
  select user_id, user_type, user_group,
      'department' as col_name, department as value
  from yourtable
) src
group by user_id, user_group, col_name

See SQL Fiddle with Demo. Your sample data does not have many values, but the result will be similar to this:

| USER_ID | USER_GROUP |   COL_NAME | HUSBAND |   WIFE |   KID1 |   KID2 |   KID3 |   KID4 |
--------------------------------------------------------------------------------------------
|       0 |         23 |    company |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         23 | department |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         23 |      fname |     asd | (null) | (null) | (null) | (null) | (null) |
|       0 |         23 |      lname |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         23 |      mname |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         23 |  nick_name |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         23 |     prefix |   sasas | (null) | (null) | (null) | (null) | (null) |
|       0 |         23 |     suffix |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 |    company |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 | department |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 |      fname |     asd | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 |      lname |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 |      mname |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 |  nick_name |  (null) | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 |     prefix |   sasas | (null) | (null) | (null) | (null) | (null) |
|       0 |         24 |     suffix |  (null) | (null) | (null) | (null) | (null) | (null) |

Note: I included additional columns to unpivot but if you only want the fname you would only include those columns in the subquery

Edit #1, if you need to keep the order of the data based on the columns in the original table, then you can add a column to the UNION ALL queries with the sort order. You can then use that column in an ORDER BY. So the query will be:

select user_id, 
  user_group,
  col_name,
  max(case when user_type = 'Husband' then value end) as Husband,
  max(case when user_type = 'wife' then value end) as Wife,
  max(case when user_type = 'kid1' then value end) as Kid1,
  max(case when user_type = 'kid2' then value end) as Kid2,
  max(case when user_type = 'kid3' then value end) as Kid3,
  max(case when user_type = 'ki4=d4' then value end) as Kid4
from
(
  select user_id, user_type, user_group,
      'prefix' as col_name, prefix as value
      , 1 as sortorder
  from yourtable
  union all
  select user_id, user_type, user_group,
      'fname' as col_name, fname as value
      , 2 as sortorder
  from yourtable
  union all
  select user_id, user_type, user_group,
      'mname' as col_name, mname as value
      , 3 as sortorder
  from yourtable
  union all
  select user_id, user_type, user_group,
      'lname' as col_name, lname as value
      , 4 as sortorder
  from yourtable
  union all
  select user_id, user_type, user_group,
      'suffix' as col_name, suffix as value
      , 5 as sortorder
  from yourtable
  union all
  select user_id, user_type, user_group,
      'nick_name' as col_name, nick_name as value
      , 6 as sortorder
  from yourtable
  union all
  select user_id, user_type, user_group,
      'company' as col_name, company as value
      , 7 as sortorder
  from yourtable
  union all
  select user_id, user_type, user_group,
      'department' as col_name, department as value
      , 8 as sortorder
  from yourtable
) src
group by user_id, user_group, col_name
order by user_group, sortorder

See SQL Fiddle with Demo