In SQL I have created a temporary table in that I want to insert multiple data into a single row with comma separated

Sravani 0
Viewed 50
CREATE TABLE #Tempdata
(
    Account_Id varchar (max)
)

INSERT INTO #Tempdata 
VALUES ('3', '4', '5');

I want data to be in single as shown in above.

tsql sql-server
2 Answers

What you want to do is:

Create #Tempdata(Account_Id varchar (max))
Insert into #Tempdata(Account_Id) values ('3,4,5');

Which would produce the following result;

| Account_Id |
| :--------: |
| 3,4,5      |

Your example would result into inserting multiple columns example if you had created your table like so:

Create table #Tempdata(column1 varchar (max), column2 varchar 
(max),column3 varchar (max)) 
Insert into #Tempdata(column1,column2,column3) values 
('3','4','5');

then your query would the following result:

| column1 | column2 | column3 |
| ------- | ------- | ------- |
|    1    |    2    |    3    |
Frank Charron0

In this code, you're going to get a table set(multiple rows):

SELECT value
FROM #Tempdata
CROSS APPLY STRING_SPLIT(Account_Id, ',');

If you want column-oriented, I mean one row and multiple columns per value, you should apply pivot on the result of the previous query.

Farshid Shekari0
Related