ETL command help needed !!

Hi,

I have the following data:

A
B
C
D

I want to convert this data like this

A B C D

Can you provide me the basic SQL command needed for this ?

Thanks
milani_lucieAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
well, that data is not what I saw before posting ...

anyhow:
DECLARE @MyVar AS VARCHAR(4000)
SET NOCOUNT ON
SELECT @MyVar = COALESCE(@MyVar + ' ', '') + FieldName
FROM TableName
 
SELECT @MyVar

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
milani_lucieAuthor Commented:
Please provide me the command. This is not PIVOT/UNPIVOT related.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
milani_lucieAuthor Commented:
I want to show the ROWS as COLUMNS. I think so your query does not return that way. Am i correct  ?

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I want to show the ROWS as COLUMNS.

well, that IS pivoting, resp crosstab.
if you want all your rows to be pivoted to columns, is being a special case, nevertheless it is called that way.
however, standard pivot statement will not do the job, though.

now, can you explain why you want that to be done?
eventually, you want to do this more effectively in the application?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ie, you might want to give more information about the "big picture"...
0
 
LowfatspreadCommented:
as angeliii say unless you can tell us what the structure of your data is there is no way we can assist in the conversion you desire.

there must be structure to your data to enable use to process it as you require...

no sql statements on there own can guarantee that

any 4 rows of data can be consistently combined
from
a
b
c
d

into

a b c d

unless there is some ordering information that is "embedded" into the data...

are you wanting the data sorted into ascending order and then converted into
rows containing the data in ascending column order?

if not explain what the sequence will be  ....

you may need to do you conversion in a standard programming language external to sql ...

SQL can only consistently process "Sets"

 
0
 
milani_lucieAuthor Commented:
Here is what i mean:

USE Sample
GO


-- Create a table


CREATE TABLE T
(
      X      int,
      Y      int,
      Z      int
)
GO


-- Insert some sample data


INSERT INTO T VALUES (1, 2, 3)
GO


-- Select the contents


SELECT X, Y, Z

FROM T

GO


-- Column As Row


SELECT 'X' AS 'Source', X AS 'Value' FROM T

UNION ALL

SELECT 'Y' AS 'Source', Y AS 'Value' FROM T

UNION ALL

SELECT 'Z' AS 'Source', Z AS 'Value' FROM T

GO

I need just opposite to this


USE Sample
GO


-- Create a table


CREATE TABLE T
(
      X      int
)
GO


-- Insert some sample data


INSERT INTO T VALUES (1)
GO

INSERT INTO T VALUES (2)
GO

INSERT INTO T VALUES (3)
GO

-- Select the contents


SELECT X

FROM T

GO


-- Row As Column


Please provide me code here.

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
milani_lucie, my request must be look like a pain, but I repeat:

you provided SQL of what you think you want, please provide the "big picture" of what you are trying to achieve.
explaining with a dummy 1 table 1 column will not help us understand why you don't want to take the crosstab procedures available on the net, for example:
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html

you said that you don't want to "PIVOT", still the method of getting rows into columns is called so.

0
All Courses

From novice to tech pro — start learning today.