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!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need advise. 1

Status
Not open for further replies.
EM1107

EM1107

IS-IT--Management
Joined
Apr 24, 2002
Messages
153
Location
CA
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.



 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Did not work it return all rows not only the one I would like to use and they are all on different rows.
 
Upvote 0 Downvote
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;
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
What were the results of either the pivot or SQL suggested by gmmastros?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
some of the data does display correctly but not all of the data is being returned.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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'.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Billz66
  • Locked
  • Question Question
need to udate table entry with number containing a *
Replies
11
Views
861
Chris Miller
Chris Miller
JustATheory
  • Locked
  • Question Question
Need to removed
Replies
3
Views
438
fredericofonseca
fredericofonseca
dgillz
  • Locked
  • Question Question
Need to concatenate comments and group by Order in Subquery
Replies
7
Views
625
dgillz
dgillz
jimoo
  • Locked
  • Question Question
Need to Comma Separate Multiple Fields together
Replies
3
Views
447
Mike Lewis
Mike Lewis
briangriffin
  • Locked
  • Question Question
Need to update with data from next record
Replies
6
Views
443
briangriffin
briangriffin

Part and Inventory Search

Sponsor

Back
Top