Convert row into column

Transact-SQL
1

I have two table.One has policyid(policy_conf_tbl) and another is policyid,policyname(policy_master_tbl).
I want to show policy_master_tbl plocy_name as column name at report time but i don`t know how many row are in policy_master_tbl.so inthis case i am not able to do this one by using pivot,when case also.
please give me idea how can i solve this problem.

2

Hi @shivampurwar,

Based on your question two tables are there,
policyid(policy_conf_tbl)
policyid,policyname(policy_master_tbl)

Only two columns you mentioned, one policy_name as column name and the other policyid as row means what are the measures in that tables.

Can you give more details abouts the columns and your desired result, it will easy to answer.

Thanks.

3

There are several ways that you can transform data from multiple rows into columns. In SQL Server you can use the PIVOT function is one of them that is used to transform the data from rows to columns.

Example:

select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
  select value, columnname
  from yourtable
) d
pivot
(
  max(value)
  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

1. You can use a CASE statement
2. Also can use the COALESCE function
**3.**Use an inline function etc.

4

sql.png1366×768 1.77 KB

I attach image related to my requirement.I have table which contain many row ,i don`t know how many.But i want all these rows(only policy_name) display as column in o/p report.In image left side policy_table is real table which contain id,policy_name,and right side o/p is a report which show all row of policy_tbl as column.

I used below solution which you provide me.But in this row field are fixed but in my table row is not fixed it can increase.In your solution Firstname, Amount, PostalCode, LastName, AccountNumber are fix row if i add one more row then i need to that row in query.so that is problem .I want dynamic solution.
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

please reply
shivampurwar92@gmail.com

5

Hi,

Plz try this one,

DECLARE @cols AS nvarchar(max),
@query AS nvarchar(max)

SELECT
@cols = STUFF((SELECT
',' + QUOTENAME(policyname)
FROM policy_master_tbl
FOR xml PATH (''), TYPE)
.value('.', 'NVARCHAR(MAX)')
, 1, 1, '')

PRINT @Cols

SET @query = 'SELECT ' + @cols + ' from
(
select policyid, [policyname]
from policy_master_tbl
) x
pivot
(
sum(policyid)
for policyname in (' + @cols + ')
) p '

EXECUTE (@query)