Click here to Skip to main content
15,868,014 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to convert all row data as column names in sql server.

the problem is that, I want to get all the data as column name without specifying column like A1,A2,B1,C1 is there is any other way to achieve the result and in here I don't want to use any aggregate function like sum or count so what is the procedure to get column name without using sum([Uid])


LIKE :INTPUT=>
ITEM
-----
A1
A2
B1
C1
OUTPUT =>
A1 | A2 | B1 | C1
-------------------
which mean column A1,A2,B1,C1

What I have tried:

Declare @Str1 nvarchar(max)
select @Str1=(SELECT TOP 1
STUFF((
        SELECT DISTINCT ',' + Items 
        FROM ItemMaster
        FOR XML PATH('')
    ), 1, 1, ''
) as columnlist
FROM ItemMaster)

select
  *
from
  (select AddPayslip,Items from ItemMaster where AddPayslip=1 and DeleteStatus=0) p
pivot (
  count([AddPayslip]) for Items in ([@Str1])
) pivoted

select @Str1;
Posted
Updated 14-Oct-22 5:43am
v7
Comments
CHill60 14-Oct-22 9:21am    
What is category meant to be? It's not in your sample data

1 solution

Here is an example of a working pivot based loosely on your sample data
SQL
declare @itemMaster table (Item varchar(3), amount decimal(15,2));
insert into @itemMaster (Item, amount) values
('A1',100),('A2',200),('B1',1000),('C1',10000);

select
  *
from
  (select Item,amount from @ItemMaster) p
pivot (
  sum([amount]) for Item in (A1, A2, B1, C1)
) pivoted
This CP article gives some guidance Simple Way To Use Pivot In SQL Query[^]

From the OP comments below I think they might just need those items in a character separated list rather than a pivot on values. Here are some ways to achieve that
1. From SQL Server 2017 you can use STRING_AGG
SQL
SELECT STRING_AGG(item, '|') as columnlist from @itemMaster

2. In earlier versions you can use FOR XML e.g.
SQL
SELECT TOP 1
STUFF((
        SELECT DISTINCT '|' + Item 
        FROM @itemMaster
        FOR XML PATH('')
    ), 1, 1, ''
) as columnlist
FROM @itemMaster
3. For even earlier versions you can use a Common Table expression e.g.
SQL
DECLARE @listStr VARCHAR(MAX) = null
;WITH cteitems AS
(
    SELECT DISTINCT Item from @itemMaster
)
SELECT  @listStr = COALESCE(@listStr+'|' ,'') + Item
FROM @itemMaster
select @listStr
All of the above give the output
A1|A2|B1|C1
without knowing what values exist
 
Share this answer
 
v2
Comments
Venila Ice 14-Oct-22 9:31am    
Yeah its correct but the problem is i want to get all the data as column name without specifying column like A1,A2,B1,C1 is there is any other way to achieve the result and in here i don't want to use any aggregate function like sum or count so what is the procedure to get column name without using sum([amount])
CHill60 14-Oct-22 9:35am    
Yes - you can use dynamic SQL - there is an example in my article Processing Loops in SQL Server[^]
CHill60 14-Oct-22 9:37am    
On the rest of it - you have to use an aggregate function - that is the syntax. What are you planning to put in the columns?
Venila Ice 14-Oct-22 9:52am    
Okey i understand now thankyou for the information
Venila Ice 14-Oct-22 10:43am    
Thankyou for you coding but sorry i didn't mean | symbol that i mean colums A1,A2,B1,C1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900