https://community.spiceworks.com/topic/973727-sql-how-to-convert-multiple-rows-into-single-rows-with-more-columns

SQL - How to convert multiple rows into single rows with more columns?

Hello Everyone,

I have a SQL report which pulls a list of orders. It returns each product on a new row, so orders with multiple products have multiple rows, 5 products max. I need to convert this report into one which has a single row for each order. Please see the attached screen shot showing the format I have and the one that is needed. My Google search pulled up cross-tab SQL queries, but in my case I am not looking to aggregate anything, simply rearrange data. Could someone please advise how this can be accomplished in Microsoft SQL Server 2012 Standard?

Thank you for your help!

• local_offer
• SQL Server 2012star4

Spice (3)Reply (15)

flagReport

AK-47

SERRANO

Enter to win a 3 Win Smart TVs (plus Disney+) AND 8 Runner Ups

Contest ends Feb 24, 2023ContestsAnswer the question to be eligible to win!Contest DetailsView all contests

checkBest Answer

Robert for Microsoft

Brand Representative for Microsoft

HABANERO

May 27th, 2015 at 1:44 AM

This is how I would get the result you want using the sample table and data you posted.

SQL

-- Create table for sample query
Declare @Orders Table (OrderID int,
	Product varchar(10),
	Quantity int);

-- Insert sample data
Insert Into @Orders
Values (100, 'Cup', 1),
	(100, 'Plate', 2),
	(101, 'Cup', 1),
	(102, 'Cup', 2),
	(103, 'Cup', 1),
	(103, 'Plate', 2),
	(103, 'Glass', 1);


With Orders
As (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
		*
	From @Orders)
Select OrderID As [Order#],
	Min(Product1) As Product1,
	Min(Quantity1) As Quantity1,
	Min(Product2) As Product2,
	Min(Quantity2) As Quantity2,
	Min(Product3) As Product3,
	Min(Quantity3) As Quantity3
From (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
		OrderID,
		'Product' + Cast(RowID as varchar) As ProductNum,
		'Quantity' + Cast(RowID as varchar) As QuantityNum,
		Product,
		Quantity
	From Orders) As Pvt
Pivot (Min(Product)
	For ProductNum In ([Product1], [Product2], [Product3])) As Pvt1
Pivot (Min(Quantity)
	For QuantityNum In ([Quantity1], [Quantity2], [Quantity3])) As Pvt2
Group By OrderID;

 

 

How to get filename from file path in MySQL?

 

SELECT id, name, SUBSTRING_INDEX(filepath, '/', -1) FROM `categories`

 https://pentestlab.blog/2018/06/04/spn-discovery/

SPN Discovery

 

Services that support Kerberos authentication require to have a Service Principal Name (SPN) associated to point users to the appropriate resource for connection. Discovery of SPNs inside an internal network is performed via LDAP queries and can assist red teams to identify hosts that are running important services such as Terminal, Exchange, Microsoft SQL etc. and being stealthy at the same time. Furthermore identification of SPNs is the first step to the kerberoasting attack.

Tim Medin explained SPN really well in his talk Attacking Kerberos: Kicking the Guard Dog of Hades with practical examples. Sean Metcalf also provided some good resources regarding SPN including an extensive list of Active Directory Service Principal Names which can be found at the end of the article.

SetSPN

SetSPN is a native windows binary which can be used to retrieve the mapping between user accounts and services. This utility can add, delete or view SPN registrations.

1	setspn -T pentestlab -Q */*

setspn – Service Discovery

Services that are bind to a domain user account and not a computer account are more likely configured with a weak password since the user has selected the password. Therefore services which they have their Canonical-Name to Users should be targeted for Kerberoasting. From the list of SPNs below the service PENTESTLAB_001 is associated with a user account.

SetSPN – SPN Records

GetUserSPNs

Tim Medin developed a PowerShell script which is part of kerberoast toolkit and can query the active directory to discover only services that are associated with a user account as a more focused approached compared to SetSPN.

1	powershell_import /root/Desktop/GetUserSPNs.ps1

GetUserSPNs – PowerShell Script

There is also a VBS script which is part of the same tookit and can provide the same information. The script can be executed from the windows command prompt by using the native Windows binary cscript.

1	cscript.exe GetUserSPNs.vbs

GetUserSPNs – VBS Script

PowerShell AD Recon

Similarly to what Tim Medin developed Sean Metcalf wrote various PowerShell scripts to perform recon against Kerberos. These scripts are part of PowerShell AD Recon repository and can query the Active Directory for interesting services such as Exchange, Microsoft SQL, Terminal etc. Sean bind each script to a specific service depending on what SPN the red teamer would like to discover. The following script will identify all the Microsoft SQL instances on the network.

1 2	powershell_import /root/Discover-PSMSSQLServers.ps1 powershell_execute Discover-PSMSSQLServers

PowerShell AD Recon – MSSQL Servers Discovery

Microsoft Exchange servers can be also discovered with the PSMSExchangeServers script.

1 2	powershell_import /root/Discover-PSMSExchangeServers.ps1 powershell_execute Discover-PSMSExchangeServers

PowerShell AD Recon – Exchange Servers Discovery

Enumeration of service accounts is important as these accounts might be configured with a weak password. The attributes PasswordLastSet and LastLogon can provide an indication of services which have a higher possibility to have a weak password set.

1 2	powershell_import /root/Find-PSServiceAccounts.ps1 powershell_execute Find-PSServiceAccounts

PowerShell AD Recon – Service Accounts

Empire

PowerShell Empire has also a module which can display Service Principal Names (SPN) for domain accounts. This module is part of the Situational Awareness category and it should be used as stealth network recon in a red team engagement.

1	usemodule situational_awareness/network/get_spn

Empire – SPN Module

The services will be presenting in the following format.

Empire – SPN Discovery

PowerShellery

Scott Sutherland before implementing the Get-SPN module to Empire had created several Powershell scripts as part of PowerShellery which can gather SPNs for various services. Some of these require PowerShell v2.0 and some other PowerShell v3.0.

1	Get-SPN -type service -search "*"

Powershellery – GetSPN

Results can be also formatted as a table for easier mapping of accounts and services.

1	Get-SPN -type service -search "*" -List yes | Format-Table

PowerShellery – GetSPN Table

There is also an additional script which can obtain the UserSID, the service and the actual User.

1 2	Import-Module .\Get-DomainSpn.psm1 Get-DomainSpn

PowerShellery – Get-DomainSpn

Impacket

Service Principal Names can be also discovered from non-joined domain systems with the python version of GetUserSPNs which is part of impacket. However valid domain credentials are required for communication with the Active Directory as token based authentication cannot be used.

1	./GetUserSPNs.py -dc-ip 10.0.0.1 pentestlab.local/test