Converting data from rows to column.

[EM1107]

Good day everyone

I hope someone can help me here.

I have to convert some data information from column to rows but I am not sure how i should do this. Bellow you will see an example of what I want to do.

The data in my table appear as follow first row being the columns of course.

ENTITYID NAME DATA
12716 NAME FOLDER 1
12716 DESCRIPTION FOLDER 1-TRAINING
12716 ORDERNUMBER 2
12716 TYPE folder
12716 LINKID 893
12716 LINKNAME FOLDER 1
12716 LIBRARY TRAINING

I want the data to appear as follow:

ENTITYID NAME DESCRIPTION ORDERNUMBER TYPE LINKID, LINKNAME LIBRARY
12716 FOLDER 1 FOLDER 1-TRAINING 2 folder 893 FOLDER 1 TRAINING

So basically what I would like to have is the column NAME values becoming the columns and the DATA column becoming the values. Anyone have an Idea as to how can do that?

Any help would be appreciated and I know your guys are the best.

 

[Andrzejek]

What a mess. If you format your post, you may get a better responce.

Is that your data?

[pre]
ENTITYID NAME DATA
12716 NAME FOLDER 1
12716 DESCRIPTION FOLDER 1-TRAINING
12716 ORDERNUMBER 2
12716 TYPE folder
12716 LINKID 893
12716 LINKNAME FOLDER 1
12716 LIBRARY TRAINING
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[EM1107]

That is correct.

I guess it got mixed after a saved the informaiton.

 

[Andrzejek]

And I assume every ENTITYID has 7 records with the same data in NAME column...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[EM1107]

that is correct.

 

[TamarGranor]

Sounds like a pivot table is what you need. Untested:

[pre]SELECT *
FROM YourTable
PIVOT(Data)
FOR Name IN (NAME,DESCRIPTION,ORDERNUMBER,TYPE,LINKID,LINKNAME,LIBRARY) AS ColField

[/pre]

The only thing I'm not sure of is whether PIVOT will work without an aggregation function. If this errors, try making that line PIVOT(MAX(Data))

Tamar

 

[dhookom]

This seems to work for me. The initial SQL objected to a field named "Name" and a derived field named "name".

WITH
cteFixNames AS
(SELECT [EntityID]
      ,[Name] as TheName
      ,[Data]
  FROM [dbo].[tblEM1107]
)
SELECT * FROM cteFixNames 
PIVOT (MIN(DATA) FOR theNAME  IN ([DESCRIPTION],[LIBRARY],[LINKID],[LINKNAME],[NAME],[ORDERNUMBER],[TYPE])) AS STC

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[EM1107]

dhookom where is the beginning of the code?
I only see a part of the code.

WITH
cteFixNames AS
(SELECT [EntityID]
,[Name] as TheName
,[Data]
FROM [dbo].[tblEM1107]

 

[EM1107]

I tried the following but it return an error because the DATA column is a varchar(max) value. Any Idea?

SELECT * FROM (
SELECT
[entityid],
[name],
[data]
FROM docsadm.registry
) tableResults
PIVOT (
SUM([data])
FOR Name IN (DESCRIPTION,ORDERNUMBER,TYPE,LINKID,LINKNAME,LIBRARY)
)
AS PivotTable

 

[SkipVought]

Every VALUE in the Data column ought to be stored as TEXT. Otherwise your db manager WILL get confused, cuz NUMBERS (that get used in math) and numerical DIGITS (that are never used in calculations) are stored differently and manipulated differently.

1 NOT EQUAL "1"

Faq68-6659

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EM1107]

Thanks guys I made some changes to the script and it now work properly. I simply specified the table I wanted instead of doing the Select * and it now work properly.

Thanks for all your suggestions.
This was greatly appreciated.

 

[Andrzejek]

Would you share your solution so others who may have the same issue may benefit...?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[EM1107]

I have no problem with that.
SELECT DESCRIPTION, ORDERNUMBER, TYPE, LINKID as DOCNUMBER, LINKNAME as DOCUMENT_NAME, LIBRARY
FROM
(
SELECT ENTITYID, NAME, DATA
FROM DOCSADM.REGISTRY
) REGISTRY_RESULT
PIVOT
(
MAX(DATA)
FOR
NAME IN (DESCRIPTION, ORDERNUMBER, TYPE, LINKID, LINKNAME, LIBRARY)
) PIV;