get column names and values in each row in SQL Server

Question

get column names and values in each row in SQL Server

dhanekulakalyan

Default port

Points: 1462
More actions
September 8, 2022 at 8:06 pm

#4085566
I Have ask from my vendor to send csv file with label and data in each row for the columns data i send them. I am trying to do in SSIS but not working , can someone please help with this?
For example I have three columns in a table and 3 rows in it but i want to get column names and values in each row.
columnName| Column Value~
Below is sql table with 3 records
Name city zip
AAA DALLAS 12345
BBB CHICAGO 99999
CCC Columbus 00000

OUTPUT FILE SHOULD BE:
Name|AAA~City|Dallas~zip|12345
Name|BBB~City|Chicago~zip|99999
Name|CCC~City|Columbus~zip|00000
- This topic was modified 2 years, 3 months ago by dhanekulakalyan.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1382 More actions · Answer 1

Why do you want to use such a non-standard format instead of just a standard CSV with column names in a header row? Or no header row if the data is uniform?

Is the data not uniform as your example implies (i.e., do some rows not have the same columns).

Even json or xml should work w/ non-uniform elements/t if you want a format (albeit bulkier, w/ that disadvantage somewhat offset by the advantage of being well-supported standards) with attribute/element names inline .

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

I suggest that you use a SQL query as the source for your dataflow, with output to a single column. Something like this:

DROP TABLE IF EXISTS #Place;

CREATE TABLE #Place
(
    Name VARCHAR(50)
   ,City VARCHAR(50)
   ,Zip VARCHAR(50)
);

INSERT #Place
(
    Name
   ,City
   ,Zip
)
VALUES
('AAA', 'Dallas', '12345')
,('BBB', 'Chicago', '99999')
,('CCC', 'Columbus', '00000');

SELECT OutCol = CONCAT('Name|', p.Name, '~City|', p.City, '~zip|', p.Zip)
FROM #Place p;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

dhanekulakalyan Default port Points: 1462 More actions · Answer 3

Thank you Phil for this idea, i will test and let you know if this is working.

This reply was modified 2 years, 3 months ago by dhanekulakalyan.

Ed B Hall of Fame Points: 3669 More actions · Answer 4

If you need to do this for more than one table or don't want to hard code the column names you can use dynamic sql. This could probably be cleaner and it doesn't handle dates very well. I don't know if SET Variable = Variable + Column works with concat, so the + chokes on integers which is why the convert is there. I couldn't be bothered to make the convert conditional based on data type which is why datetimes get mangled.

With hindsight I would hard code the query as suggested by Phil.

DROP TABLE IF EXISTS dbo.SampleData
CREATE TABLE dbo.SampleData
(   ID INT IDENTITY(1,1),
    TheName VARCHAR(100),
    City VARCHAR(100),
    Zip VARCHAR(10),
    InsertDate DATETIME
)

INSERT dbo.SampleData
VALUES ('AAA', 'DALLAS', '12345', GETDATE()), ('BBB', 'CHICAGO', '99999', GETDATE()), ('CCC', 'COLUMBUS', '00000', GETDATE())


DECLARE @TableName VARCHAR(100) = 'SampleData',
        @TableSchema VARCHAR(100) = 'DBO',
        @List NVARCHAR(4000) = '',
        @SQL NVARCHAR(MAX)


SELECT  @List = @List + CONCAT('''',COLUMN_NAME,'|''+' , CONCAT('ISNULL(CONVERT(VARCHAR(100),', COLUMN_NAME,'),'''')'), '+','''∼'''+'+')
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @TableName
AND     TABLE_SCHEMA = @TableSchema
ORDER   BY ORDINAL_POSITION


SET @SQL = CONCAT('SELECT ',    SUBSTRING(@List,1,(LEN(@List)-5)), ' AS ColumnList FROM ', @TableSchema, '.', @TableName)

--SELECT @SQL

EXEC SP_EXECUTESQL @Query = @SQL

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 5

Here is another method - not dynamic, but easily extensible:

Declare @sampleData table (Name varchar(50), City varchar(50), Zip char(5));
 Insert Into @sampleData (Name, City, Zip)
 Values ('AAA', 'Dallas', '12345')
      , ('BBB', 'Chicago', '99999')
      , ('CCC', 'Columbus', '00000');
      
Declare @columnMap table (Position smallint, ColumnName varchar(50));
 Insert Into @columnMap (Position, ColumnName)
 Values (1, 'Name'), (2, 'City'), (3, 'Zip')
 
Declare @fieldDelimiter char(1) = '|'
      , @columnDelimiter char(1) = '~';

 Select row_value = string_agg(concat_ws(@fieldDelimiter, cm.ColumnName, f.FieldValue), @columnDelimiter)
   From @sampleData     sd
  Cross Join @columnMap cm
  Cross Apply (Values (choose(cm.Position, sd.Name, sd.City, sd.Zip))) As f(FieldValue)
  Group By sd.Name;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

dhanekulakalyan wrote:

Thank you Phil for this idea, i will test and let you know if this is working.

It would still be nice to know the need for this particular format. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 7

And if you don't like mixing cross join and cross apply - or using the table variable, you can embed the column mapping into the query as follows:

Declare @fieldDelimiter char(1) = '|'
      , @columnDelimiter char(1) = '~';

 Select row_value = string_agg(concat_ws(@fieldDelimiter, cm.ColumnName, f.FieldValue), @columnDelimiter)
   From @sampleData                                                     As sd
  Cross Apply (Values (1, 'Name'), (2, 'City'), (3, 'Zip'))             As cm(Position, ColumnName)
  Cross Apply (Values (choose(cm.Position, sd.Name, sd.City, sd.Zip)))  As  f(FieldValue)
  Group By sd.Name, sd.City, sd.Zip;