Need advise. 1

EM1107 · Dec 22, 2017

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.

dhookom · Dec 22, 2017

I expect you could use SQL like:

SQL:

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

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

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

EM1107 · Dec 22, 2017

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

EM1107 · Dec 22, 2017

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;

dhookom · Dec 22, 2017

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

Could you try include the ID column:

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

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

EM1107 · Dec 22, 2017

If I use your suggestion to use the id I get more result but I also get null rows that I do not desire.

I have attach a sample that I am trying to get.

gmmastros · Dec 22, 2017

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

dhookom · Dec 22, 2017

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:

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

EM1107 · Dec 22, 2017

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

Andrzejek · Dec 22, 2017

Is that your data....?

[pre]
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
[/pre]

---- Andy

There is a great need for a sarcasm font.

dhookom · Dec 22, 2017

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

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

EM1107 · Jan 2, 2018

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

dhookom · Jan 2, 2018

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:

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

Leo Dunn · Jan 18, 2018

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

EM1107 · Jan 18, 2018

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'.

dhookom · Jan 18, 2018

What is the exact SQL you are using?

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

gmmastros · Jan 18, 2018

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need advise. 1

EM1107

IS-IT--Management

dhookom

Programmer

EM1107

IS-IT--Management

EM1107

IS-IT--Management

dhookom

Programmer

EM1107

IS-IT--Management

gmmastros

Programmer

dhookom

Programmer

EM1107

IS-IT--Management

Andrzejek

Programmer

dhookom

Programmer

EM1107

IS-IT--Management

dhookom

Programmer

Leo Dunn

Instructor

EM1107

IS-IT--Management

dhookom

Programmer

gmmastros

Programmer

Similar threads

Part and Inventory Search

Sponsor