×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Need advise.

Need advise.

Need advise.

(OP)
Good day everyone.

Here is my situation.

I have a table with 3 columns
The first column is a link column the second column is called name and the third is called data.
The column called name include different name like name, description, type and so on.
The data is the result of these column.
For instance if the name was called 'Date', data would represent the actual date.
if the data column would say 'description' the data column would show the description.

Here is an example.

ID NAME DATA
1 Date 1/1/2012
1 Description Manual
1 Type book
1 Quantity 5
So what I am looking for is a result as follow.

ID DATE DESCRIPTION TYPE QUANTITY
1 1/1/2012 Manual Book 5

Has you can see above, the value in the columns become a single row instead of having 3 columns and have multiple time the same ID, I get 5 column for one single id value.

Any help would be appreciate.
I have tried different options and none of them work.



RE: Need advise.

I expect you could use SQL like:

CODE --> SQL

SELECT * FROM TableWith3Columns
PIVOT (MIN(DATA) FOR NAME
IN ([Date],[Description],[Type],[Quantity])) as Result 

ID	Date		Description	Type	Quantity
1	1/1/2012	Manual		Book	5
2	2/1/2012	Quick Ref	On Line	55 

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

RE: Need advise.

(OP)
Did not work it return all rows not only the one I would like to use and they are all on different rows.

RE: Need advise.

(OP)
I tried the following but it only return one row and I have multiple rows that should show.

select DESCRIPTION, ordernumber, TYPE, linkid, LINKNAME, library
from
(
select name, data
from dbo.table_name

) d
pivot
(
max(data)
for
name in (description, ordernumber, type, linkid, LINKNAME, library)

) piv;

RE: Need advise.

Can you provide more of your actual data and desired results?

Could you try include the ID column:

CODE --> SQL

select DESCRIPTION, ordernumber, TYPE, linkid, LINKNAME, library
from
(
select ID, name, data
from dbo.table_name
) d
pivot
(
max(data)
for 
name in (description, ordernumber, type, linkid, LINKNAME, library)
) piv; 

Or more simply

CODE --> SQL

SELECT * FROM table_Name 
PIVOT (MIN(DATA) FOR NAME
IN ([Date],[Description],[Type],[Quantity],Ordernumber, LinkName, linkid,library)) as Result 

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

RE: Need advise.

Here's another method. In my opinion, this is easier to understand than the perfectly acceptable pivot method shown by dhookom.

CODE

Declare @Temp Table(ID Int, NAME VarChar(100), DATA VarChar(100))

Insert Into @Temp Values(1, 'Date', '1/1/2012')
Insert Into @Temp Values(1, 'Description', 'Manual')
Insert Into @Temp Values(1, 'Type', 'book')
Insert Into @Temp Values(1, 'Quantity', '5')

Select	Id,
	Max(Case When Name = 'Description' Then Data End) As Description,
	Max(Case When Name = 'Type' Then Data End) As Type,
	Max(Case When Name = 'Quantity' Then Data End) As Quantity
From	@Temp
Group By Id 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Need advise.

Actual data and more results please? Can you script this to make it easier for us to assist? Images help but just make more work for anyone attempting to help you.

Something like this:

CODE --> sql

/****** Object:  Table [dbo].[Table_Name]    Script Date: 12/22/2017 12:07:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_Name](
	[ID] [int] NOT NULL,
	[NAME] [varchar](50) NULL,
	[DATA] [varchar](50) NULL,
	[ENTITYID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'Date', N'1/1/2012', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'Description', N'Manual', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'Type', N'Book', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'Quantity', N'5', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'Date', N'2/1/2012', 222)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'Description', N'Quick Ref', 222)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'Type', N'On Line', 222)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'Quantity', N'55', 222)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'OrderNumber', N'111', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'LinkName', N'LinkName 1', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'Library', N'Library 1', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'OrderNumber', N'Order Number 2', 222)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'LinkName', N'Link Name 2', 222)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'Library', N'Library 2', 222)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (1, N'LinkID', N'Link ID 1', 11124)
GO
INSERT [dbo].[Table_Name] ([ID], [NAME], [DATA], [ENTITYID]) VALUES (2, N'LinkID', N'Link ID 2', 222)
GO 

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

RE: Need advise.

(OP)
Here is an other data sample but this time it is real data.
The data I wan to retrieve is located in the Name and data column
The ENTITYID column link the object that belong together.

SYSTEM_ID TYPE ENTITYSCOPE ENTITYID NAME DATATYPE DATA
11120 PROPERTY REGISTRY 11119 NAME String New Workspace
11121 PROPERTY REGISTRY 11119 DESCRIPTION String
11122 PROPERTY REGISTRY 11119 DATE String 01/28/2011
11125 PROPERTY REGISTRY 11124 NAME String 1
11126 PROPERTY REGISTRY 11124 DESCRIPTION String 1-DMRMDB
11128 PROPERTY REGISTRY 11124 TYPE String folder
11129 PROPERTY REGISTRY 11124 LINKID String 19
11133 PROPERTY REGISTRY 11132 NAME String folder test
11134 PROPERTY REGISTRY 11132 DESCRIPTION String folder test-DMRMDB
11136 PROPERTY REGISTRY 11132 TYPE String folder
11137 PROPERTY REGISTRY 11132 LINKID String 6
11141 PROPERTY REGISTRY 11140 NAME String New test folder
11142 PROPERTY REGISTRY 11140 DESCRIPTION String New test folder-DMRMDB
11144 PROPERTY REGISTRY 11140 TYPE String folder
11145 PROPERTY REGISTRY 11140 LINKID String 7
11149 PROPERTY REGISTRY 11148 NAME String tsuser Folder
11150 PROPERTY REGISTRY 11148 DESCRIPTION String tsuser Folder-DMRMDB
11152 PROPERTY REGISTRY 11148 TYPE String folder
11153 PROPERTY REGISTRY 11148 LINKID String 17
11930 PROPERTY REGISTRY 11929 NAME String New Workspace
11931 PROPERTY REGISTRY 11929 DESCRIPTION String
11932 PROPERTY REGISTRY 11929 DATE String 09/29/2011
11935 PROPERTY REGISTRY 11934 NAME String test
11936 PROPERTY REGISTRY 11934 DESCRIPTION String test-DMRMDB
11938 PROPERTY REGISTRY 11934 TYPE String folder
11939 PROPERTY REGISTRY 11934 LINKID String 398
17502 PROPERTY REGISTRY 17501 NAME String test1
17503 PROPERTY REGISTRY 17501 DESCRIPTION String
17504 PROPERTY REGISTRY 17501 DATE String 09/12/2013
17507 PROPERTY REGISTRY 17506 NAME String @01!
17508 PROPERTY REGISTRY 17506 DESCRIPTION String @01!-DMDB
17510 PROPERTY REGISTRY 17506 TYPE String document
17511 PROPERTY REGISTRY 17506 LINKID String 767
17515 PROPERTY REGISTRY 17514 NAME String test2
17516 PROPERTY REGISTRY 17514 DESCRIPTION String
17517 PROPERTY REGISTRY 17514 DATE String 09/12/2013

RE: Need advise.

Is that your data....?

SYSTEM_ID   TYPE   ENTITYSCOPE ENTITYID NAME        DATATYPE DATA
 11120    PROPERTY    REGISTRY    11119 NAME        String   New Workspace
 11121    PROPERTY    REGISTRY    11119 DESCRIPTION String 
 11122    PROPERTY    REGISTRY    11119 DATE        String   01/28/2011
 11125    PROPERTY    REGISTRY    11124 NAME        String   1
 11126    PROPERTY    REGISTRY    11124 DESCRIPTION String   1-DMRMDB
 11128    PROPERTY    REGISTRY    11124 TYPE        String   folder
 11129    PROPERTY    REGISTRY    11124 LINKID      String   19
 11133    PROPERTY    REGISTRY    11132 NAME        String   folder test
 11134    PROPERTY    REGISTRY    11132 DESCRIPTION String   folder test-DMRMDB
 11136    PROPERTY    REGISTRY    11132 TYPE        String   folder
 11137    PROPERTY    REGISTRY    11132 LINKID      String   6
 11141    PROPERTY    REGISTRY    11140 NAME        String   New test folder
 11142    PROPERTY    REGISTRY    11140 DESCRIPTION String   New test folder-DMRMDB
 11144    PROPERTY    REGISTRY    11140 TYPE        String   folder
 11145    PROPERTY    REGISTRY    11140 LINKID      String   7
 11149    PROPERTY    REGISTRY    11148 NAME        String   tsuser Folder
 11150    PROPERTY    REGISTRY    11148 DESCRIPTION String   tsuser Folder-DMRMDB
 11152    PROPERTY    REGISTRY    11148 TYPE        String   folder
 11153    PROPERTY    REGISTRY    11148 LINKID      String   17
 11930    PROPERTY    REGISTRY    11929 NAME        String   New Workspace
 11931    PROPERTY    REGISTRY    11929 DESCRIPTION String 
 11932    PROPERTY    REGISTRY    11929 DATE        String   09/29/2011
 11935    PROPERTY    REGISTRY    11934 NAME        String   test
 11936    PROPERTY    REGISTRY    11934 DESCRIPTION String   test-DMRMDB
 11938    PROPERTY    REGISTRY    11934 TYPE        String   folder
 11939    PROPERTY    REGISTRY    11934 LINKID      String   398
 17502    PROPERTY    REGISTRY    17501 NAME        String   test1
 17503    PROPERTY    REGISTRY    17501 DESCRIPTION String 
 17504    PROPERTY    REGISTRY    17501 DATE        String   09/12/2013
 17507    PROPERTY    REGISTRY    17506 NAME        String   @01!
 17508    PROPERTY    REGISTRY    17506 DESCRIPTION String   @01!-DMDB
 17510    PROPERTY    REGISTRY    17506 TYPE        String   document
 17511    PROPERTY    REGISTRY    17506 LINKID      String   767
 17515    PROPERTY    REGISTRY    17514 NAME        String   test2
 17516    PROPERTY    REGISTRY    17514 DESCRIPTION String 
 17517    PROPERTY    REGISTRY    17514 DATE        String   09/12/2013 
 


---- Andy

There is a great need for a sarcasm font.

RE: Need advise.

What were the results of either the pivot or SQL suggested by gmmastros?

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

RE: Need advise.

(OP)
some of the data does display correctly but not all of the data is being returned.

RE: Need advise.

You have a column named "name" and a data value of "name" so this causes an issue. Personally I would never name anything name since it potentially leads to issues like this.

You might try providing an alias for NAME in a cte with a pivot like the following:

CODE --> sql

WITH cteAll as
(SELECT EntityID,NAME TheName, DATA 
 FROM vwYourTableName)

SELECT * FROM cteAll  
PIVOT (MIN(DATA) FOR TheName 
IN ([Date],[Description],[Type],[Quantity],LinkID, Ordernumber, Name, library)) as Result 

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

RE: Need advise.

The first reply by dhoomkom must have worked well. There may be any error inserting the values to the database. Can you please recheck the data inserted to the Table?


Leo
Essays Chief

RE: Need advise.

(OP)
Sorry It does not work. If I run the query as describe there I get the following error.

Msg 265, Level 16, State 1, Line 3
The column name "Type" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 3
The column 'Type' was specified multiple times for 'Result'.

RE: Need advise.

What is the exact SQL you are using?

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

RE: Need advise.

Try this:

CODE

Declare @Temp 
Table	(
	SYSTEM_ID Int,
	TYPE varchar(20),
	ENTITYSCOPE varchar(20),
	ENTITYID Int,
	NAME VarChar(20),
	DATATYPE varchar(20),
	DATA VarChar(40)
	)

Insert Into @Temp Values(11120,	'PROPERTY', 'REGISTRY',	11119,	'NAME',			'String',	'New Workspace')
Insert Into @Temp Values(11121,	'PROPERTY', 'REGISTRY',	11119,	'DESCRIPTION',	'String',	'')
Insert Into @Temp Values(11122,	'PROPERTY', 'REGISTRY',	11119,	'DATE',			'String',	'01/28/2011')
Insert Into @Temp Values(11125,	'PROPERTY', 'REGISTRY',	11124,	'NAME',			'String',	'1')
Insert Into @Temp Values(11126,	'PROPERTY', 'REGISTRY',	11124,	'DESCRIPTION',	'String',	'1-DMRMDB')
Insert Into @Temp Values(11128,	'PROPERTY', 'REGISTRY',	11124,	'TYPE',			'String',	'folder')
Insert Into @Temp Values(11129,	'PROPERTY', 'REGISTRY',	11124,	'LINKID',		'String',	'19')
Insert Into @Temp Values(11133,	'PROPERTY', 'REGISTRY',	11132,	'NAME',			'String',	'folder test')
Insert Into @Temp Values(11134,	'PROPERTY', 'REGISTRY',	11132,	'DESCRIPTION',	'String',	'folder test-DMRMDB')
Insert Into @Temp Values(11136,	'PROPERTY', 'REGISTRY',	11132,	'TYPE',			'String',	'folder')
Insert Into @Temp Values(11137,	'PROPERTY', 'REGISTRY',	11132,	'LINKID',		'String',	'6')
Insert Into @Temp Values(11141,	'PROPERTY', 'REGISTRY',	11140,	'NAME',			'String',	'New test folder')
Insert Into @Temp Values(11142,	'PROPERTY', 'REGISTRY',	11140,	'DESCRIPTION',	'String',	'New test folder-DMRMDB')
Insert Into @Temp Values(11144,	'PROPERTY', 'REGISTRY',	11140,	'TYPE',			'String',	'folder')
Insert Into @Temp Values(11145,	'PROPERTY', 'REGISTRY',	11140,	'LINKID',		'String',	'7')
Insert Into @Temp Values(11149,	'PROPERTY', 'REGISTRY',	11148,	'NAME',			'String',	'tsuser Folder')
Insert Into @Temp Values(11150,	'PROPERTY', 'REGISTRY',	11148,	'DESCRIPTION',	'String',	'tsuser Folder-DMRMDB')
Insert Into @Temp Values(11152,	'PROPERTY', 'REGISTRY',	11148,	'TYPE',			'String',	'folder')
Insert Into @Temp Values(11153,	'PROPERTY', 'REGISTRY',	11148,	'LINKID',		'String',	'17')
Insert Into @Temp Values(11930,	'PROPERTY', 'REGISTRY',	11929,	'NAME',			'String',	'New Workspace')
Insert Into @Temp Values(11931,	'PROPERTY', 'REGISTRY',	11929,	'DESCRIPTION',	'String',	'')
Insert Into @Temp Values(11932,	'PROPERTY', 'REGISTRY',	11929,	'DATE',			'String',	'09/29/2011')
Insert Into @Temp Values(11935,	'PROPERTY', 'REGISTRY',	11934,	'NAME',			'String',	'test')
Insert Into @Temp Values(11936,	'PROPERTY', 'REGISTRY',	11934,	'DESCRIPTION',	'String',	'test-DMRMDB')
Insert Into @Temp Values(11938,	'PROPERTY', 'REGISTRY',	11934,	'TYPE',			'String',	'folder')
Insert Into @Temp Values(11939,	'PROPERTY', 'REGISTRY',	11934,	'LINKID',		'String',	'398')
Insert Into @Temp Values(17502,	'PROPERTY', 'REGISTRY',	17501,	'NAME',			'String',	'test1')
Insert Into @Temp Values(17503,	'PROPERTY', 'REGISTRY',	17501,	'DESCRIPTION',	'String',	'')
Insert Into @Temp Values(17504,	'PROPERTY', 'REGISTRY',	17501,	'DATE',			'String',	'09/12/2013')
Insert Into @Temp Values(17507,	'PROPERTY', 'REGISTRY',	17506,	'NAME',			'String',	'@01!')
Insert Into @Temp Values(17508,	'PROPERTY', 'REGISTRY',	17506,	'DESCRIPTION',	'String',	'@01!-DMDB')
Insert Into @Temp Values(17510,	'PROPERTY', 'REGISTRY',	17506,	'TYPE',			'String',	'document')
Insert Into @Temp Values(17511,	'PROPERTY', 'REGISTRY',	17506,	'LINKID',		'String',	'767')
Insert Into @Temp Values(17515,	'PROPERTY', 'REGISTRY',	17514,	'NAME',			'String',	'test2')
Insert Into @Temp Values(17516,	'PROPERTY', 'REGISTRY',	17514,	'DESCRIPTION',	'String',	'')
Insert Into @Temp Values(17517,	'PROPERTY', 'REGISTRY',	17514,	'DATE',			'String',	'09/12/2013')

Select	EntityId,
	Max(Case When Name = 'Description' Then Data End) As Description,
	Max(Case When Name = 'Type' Then Data End) As Type,
	Max(Case When Name = 'Date' Then Data End) As Quantity,
	Max(Case When Name = 'LinkId' Then Data End) As LinkId
From	@Temp
Group By EntityId 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close