How to change Column with row in SQL

Question

Post reply

How to change Column with row in SQL

a913125

Valued Member

Points: 66
More actions
August 15, 2016 at 4:48 am

#400574

Hello
I Have a Table with this Contents:
How I Can Get Below Table from A bow table:
by the way you can create table with below Script:
CREATE TABLE [dbo].[tblTypes](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[ComputerID] [int] NULL,
[TypeDescription] [nvarchar](150) NULL,
[TypeValue] [nvarchar](150) NULL,
CONSTRAINT [PK_tblTypes] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and with below Script you can add data to table:
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
PRINT(N'Add 15 rows to [dbo].[tblTypes]')
SET IDENTITY_INSERT [dbo].[tblTypes] ON
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (1, 1, N'Ram', N'256')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (2, 1, N'Hdd', N'500Gig')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (3, 1, N'Fan', N'3Fan')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (4, 1, N'Case', N'BigGreen')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (5, 1, N'Power', N'500W')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (6, 2, N'Ram', N'512')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (7, 2, N'Hdd', N'2TR')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (8, 2, N'Fan', N'2Fan')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (9, 2, N'Case', N'LowRed')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (10, 2, N'Power', N'200W')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (11, 3, N'Ram', N'256*2')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (12, 3, N'Hdd', N'800Gig')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (13, 3, N'Fan', N'1Fan')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (14, 3, N'Case', N'Lower')
INSERT INTO [dbo].[tblTypes] ([RowID], [ComputerID], [TypeDescription], [TypeValue]) VALUES (15, 3, N'Power', N'360W')
SET IDENTITY_INSERT [dbo].[tblTypes] OFF
COMMIT TRANSACTION
GO

Viewing 3 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You can use a method called cross tabs (or the pivot operator) which is explained in here:

http://www.sqlservercentral.com/articles/T-SQL/63681/

Here's an example based on your greatly posted sample data.

SELECT ComputerID,

MAX(CASE WHEN TypeDescription = 'Ram' THEN TypeValue ELSE '' END) AS Ram,

MAX(CASE WHEN TypeDescription = 'Hdd' THEN TypeValue ELSE '' END) AS HDD

FROM dbo.tblTypes

GROUP BY ComputerID;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

a913125 Valued Member Points: 66 More actions · Answer 2

Very Very Very Thanks

Many Many Many Thanks

:kiss: :kiss: :kiss: :kiss: :kiss: :kiss: :kiss:

Luis Cazares (8/15/2016)
You can use a method called cross tabs (or the pivot operator) which is explained in here:
http://www.sqlservercentral.com/articles/T-SQL/63681/
Here's an example based on your greatly posted sample data.
SELECT ComputerID,
MAX(CASE WHEN TypeDescription = 'Ram' THEN TypeValue ELSE '' END) AS Ram,
MAX(CASE WHEN TypeDescription = 'Hdd' THEN TypeValue ELSE '' END) AS HDD
FROM dbo.tblTypes
GROUP BY ComputerID;