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

3 answers: 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
25 3

Awesome . It worked like a charm .

Apr 20 at 08:32 PM kalpu234

@kalpu234 : OMG NO! THIS IS DOOMED TO FAILURE FROM THE GIT! DO NOT USE THIS METHOD! AS JOHNM STATED, THERE IS NOTHING TO GUARANTEE THE CORRECT ORDER OF ROWS! Please see my post on why this method should NEVER be used! And, yes, I had to down-vote it because it's actually dangerous code.

Apr 30 at 03:42 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

Without the OP posting the PoSH that created this output, I cannot provide the correct answer. I can only tell you that the accepted answer by @gdk is doomed to swift and immediate failure. DO NOT USE THAT ANSWER! Here's the proof...

First, I cleaned up the posted code:

 DROP TABLE #temp
 
  CREATE TABLE #temp (ColName VARCHAR(400),Value VARCHAR(400) );
 
  INSERT INTO #temp (ColName,Value) 
  VALUES  ('Name','Instance1')
         ,('SystemName','ServerA')       --Make note of this row on Instance1
         ,('State','Stopped')
         ,('StartMode','Manual')
         ,('StartName','Localsystem')    --Make note of this row on Instance1
 
         ,('Name','Instance2')
         ,('SystemName','ServerB')       --Make note of this row on Instance2
         ,('State','Running')
         ,('StartMode','Automatic')
         ,('StartName','ServiceAccount1')--Make note of this row on Instance2
 
 ;
 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
 ;

This is what I got for the answer. Notice that the rows marked above HAVE BEEN REVERSED ACCORDING TO THE RID! (I've emphasized the errors with asterisks).

     Name         SystemName     State      StartMode   StartName
     Instance1   **ServerB**     Stopped    Manual      **ServiceAccount1**
     Instance2   **ServerA**     Running    Automatic   **Localsystem**

You may not get the same error when you run the code on your box and that's the really deceptive part. Sometimes it will look like it worked correctly but without some manner to associate all of the rows to the correct server instance, this method is doomed to failure. DON"T USE THIS METHOD AS IT IS!!!!

more ▼

answered Apr 30 at 04:03 PM

avatar image

Jeff Moden
2.3k 3 7 13

(comments are locked)
10|1200 characters needed characters left

Thanks @Jeff Moden. You are right the order of the output changes, i would like to correct my answer I think to order the table i can do the following First i would add CREATE TABLE #temp ( id INT IDENTITY(1, 1) , ColName VARCHAR(400) , Value VARCHAR(400) ); and in the pivot
SELECT [Name] , [SystemName] , [State] , [StartMode] , [StartName] FROM ( SELECT ColName , Value , ROW_NUMBER() OVER ( PARTITION BY ( ColName ) ORDER BY id ) AS RID FROM #temp ) AS SOURCETABLE PIVOT( MAX(Value) FOR ColName IN ( [Name], [SystemName], [State], [StartMode], [StartName] ) ) AS PIVOTTABLE

more ▼

answered Apr 30 at 11:59 PM

avatar image

gdk
25 3

@gdk ... you're a good person for coming back on this. Thank you. The first part is correct. You need a table with some form of auto-numbering column. The key then is the order in which you populate it and HOW you populate it. Even if the table is guaranteed to be populated by a single source from a single user, there is no guarantee that the IDENTITY column will reflect the correct order unless you do 1 of 2 things. Either use an ORDER BY on a column that represents the correct order (no such thing for the OP's original problem of PoSh output) or insert rows in the correct order one at a time. Only then would code similar to the above be guaranteed to work correctly. The best way would be to have the PoSH script number the rows as they're produced OR have all columns for each instance on a single row. To do a serial RBAR (Row By Agonizing Row) insert, you could output the PoSH to a file and then use something like BCP or BULK insert to insert the rows one at a time. There is a 3rd way but most people aren't allowed to use xp_CmdShell (which works very well here with no security risk). Then, your good code would work perfectly.

May 02 at 01:52 AM Jeff Moden

@kalpu234 ,

Any interest in how to solve this problem correctly? If so, I need the PowerShell script you used to that I can demonstrate. Otherwise, I wish you all the luck in the world because the answer that you selected as the solution to your problem is actually broken and will give your the wrong answers as I've demonstrated in my "don't use that answer" post on this thread.

May 05 at 11:36 PM Jeff Moden
(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:

x1084

asked: Apr 19 at 10:34 PM

Seen: 135 times

Last Updated: May 05 at 11:36 PM

Copyright 2017 Redgate Software. Privacy Policy