x

How to display rows into column

Below is my data in the table which the output from the power shell script loaded into SQL table

 CREATE TABLE #temp ( ColName VARCHAR(400) )
 
 INSERT  INTO #temp
         SELECT  'Name       : Inatnce1'
 go 
 INSERT  INTO #temp
         SELECT  'SystemName : ServerA'
 go
 INSERT  INTO #temp
         SELECT  'State      : Stopped'
 go
 INSERT  INTO #temp
         SELECT  'StartMode  : Manual'
 go 
 INSERT  INTO #temp
         SELECT  'StartName  : Localsystem'
 go 
 INSERT  INTO #temp
         SELECT  'SystemName : ServerB'
 go 
 INSERT  INTO #temp
         SELECT  'State      : Running'
 go 
 INSERT  INTO #temp
         SELECT  'StartMode  : Acutomatic'
 go 
 INSERT  INTO #temp
         SELECT  'StartName  : serviceAccount1'
 go 
 
 DROP TABLE #temp



--So, the Current data looks like below

ColName

Name : Inatnce1

SystemName : ServerA

State : Stopped

StartMode : Manual

StartName : Localsystem

Name : Instance2

SystemName : ServerB

State : Running

StartMode : Automatic

StartName : serviceAccount1

The output that I am trying to get from above table is . Any help will be appreciated

Name SystemName State StartMode StartName

Inatnce1 ServerA Stopped Manual Localsystem

Instance2 ServerB Running Automatic serviceAccount1

more ▼

asked Apr 19 at 10:34 PM in Default

avatar image

kalpu234
72 4 8 9

How do you know that "State: Stopped" refers to ServerA? Is there anything identifying that row to that particular server?

Apr 20 at 02:04 PM JohnM
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

See if this help

DROP TABLE #temp

CREATE TABLE #temp (ColName VARCHAR(400),Value VARCHAR(400) )

INSERT INTO #temp (ColName,Value) VALUES ('Name','Instance1'), ('SystemName','ServerA'), ('State','Stopped'), ('StartMode','Manual'), ('StartName','Localsystem'), ('Name','Instance2'), ('SystemName','ServerB'), ('State','Running'), ('StartMode','Automatic'), ('StartName','ServiceAccount1');

SELECT * FROM #temp

SELECT [Name],[SystemName],[State],[StartMode],[StartName] FROM (SELECT ColName,Value,ROW_NUMBER() OVER (PARTITION BY (ColName)ORDER BY ColName) AS RID
FROM #temp) AS SOURCETABLE PIVOT ( MAX (Value) FOR ColName IN ([Name],[SystemName],[State],[StartMode],[StartName]) ) AS PIVOTTABLE

more ▼

answered Apr 20 at 08:02 PM

avatar image

gdk
21 1

Awesome . It worked like a charm .

Apr 20 at 08:32 PM kalpu234
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1081

asked: Apr 19 at 10:34 PM

Seen: 60 times

Last Updated: Apr 20 at 08:32 PM

Copyright 2016 Redgate Software. Privacy Policy