Pivot or convert column to rows

Question

Post reply

Pivot or convert column to rows

Saran

Right there with Babe

Points: 760

December 13, 2019 at 3:51 pm

#3707714

Hi All,

I have table which stores all in single column, I want to take a data only for particular vendor name along with the server name where the tool is installed.

Like,

Server name, Tool Name

----

CREATE TABLE [dbo].[result](

[id] [int] IDENTITY(1,1) NOT NULL,

[Data] [nvarchar](255) NULL,

[Item] [nvarchar](255) NULL

)

insert into [result] values ('server:server1','server')

insert into [result] values ('Name : VM Tool','Name')

insert into [result] values ('Version : 10.0.9.3917699','Version')

insert into [result] values ('Vendor : VMware, Inc.','Vendor')

insert into [result] values ('Caption : VMware Tools','Caption')

 

insert into [result] values ('server:server1','server')

insert into [result] values ('Name : VS studio','Name')

insert into [result] values ('Version : 10.0.9.3917699','Version')

insert into [result] values ('Vendor : MS','Vendor')

insert into [result] values ('Caption : MS VS','Caption')

insert into [result] values ('server:server1','server')

insert into [result] values ('Name : VS studio','Name')

insert into [result] values ('Version : 10.0.9.3917699','Version')

insert into [result] values ('Vendor : MS','Vendor')

insert into [result] values ('Caption : MS VS','Caption')

This topic was modified 5 years ago by Saran.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

as_1234 SSCrazy Points: 2865 More actions · Answer 1

Apologises if I missed something but where is the link between each vendor and server name? The only link I can see is the order of the insert statements. Unfortunately you can't guarantee that SQL will return the results in the same order. Unless I've misunderstood you need another column to specify which rows of data are part of the same vendor/software combination.

I am not intending to criticise, I only ask the question to understand.

If possible you may be better with a different table design which has specific columns for server, name, version e.t.c. How much control do you have over the table design?

Saran Right there with Babe Points: 760 More actions · Answer 2

Thanks for reply, We just got this as excel and loaded into a database. I am trying to get data

Server name, Tool Name

server1, VM

Server1, VS studio

Server2, SQL server

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

as1981 is right - there is nothing to link the server name to the tool name. A table is an unordered set, so SQL Server can't assume that "VM" goes with "server1" just because it appears in the next row. If this comes from a spreadsheet, have you tried pivoting in Excel before loading into the database?

John

Saran Right there with Babe Points: 760 More actions · Answer 4

Saran

Right there with Babe

Points: 760

December 13, 2019 at 5:03 pm

#3707740

Thanks. I will have a look on it.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

If the inserts are done in a particular order and the order is actually preserved by the ID column, this could be pretty easy to pull of. You just need that guarantee as to order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2865 More actions · Answer 6

Sqltester1: Jeff Moden is correct. Sorry I didn't think of the possibility of using the ID column. Perhaps let us know if you are able to pivot the data in Excel?

If you still need to use the database to create the pivot have you been able to try any queries?