Need help on Dynamic Column

Question

Post reply

Need help on Dynamic Column

Adelia

SSC-Addicted

Points: 422

December 12, 2019 at 4:19 pm

Go to Answer

#3707449

I've table and data as following,

CREATE TABLE [dbo].[simulateTable](
[UniqueId] [int] NOT NULL,
[AKT] [nvarchar](50) NULL,
[PST] [nvarchar](50) NULL,
 CONSTRAINT [PK_simulateTable] PRIMARY KEY CLUSTERED 
(
[UniqueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (1, N'13', N'62')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (2, N'113', N'508')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (3, N'123', N'351')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (4, N'113', N'174')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (5, N'123', N'347')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (6, N'63', N'12')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (7, N'113', N'141')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (8, N'113', N'158')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (9, N'13', N'61')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (10, N'63', N'7')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (11, N'123', N'347')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (12, N'113', N'132')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (13, N'113', N'141')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (14, N'113', N'124')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (15, N'113', N'133')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (16, N'13', N'76')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (17, N'13', N'76')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (18, N'0', N'0')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (19, N'123', N'365')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (20, N'63', N'13')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (21, N'123', N'353')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (22, N'63', N'10')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (23, N'123', N'342')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (24, N'113', N'166')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (25, N'123', N'369')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (26, N'113', N'161')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (27, N'113', N'508')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (28, N'113', N'171')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (29, N'113', N'172')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (30, N'13', N'95')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (31, N'123', N'366')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (32, N'123', N'350')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (33, N'63', N'17')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (34, N'113', N'210')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (35, N'113', N'180')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (36, N'113', N'200')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (37, N'113', N'180')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (38, N'123', N'353')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (39, N'63', N'13')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (40, N'123', N'352')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (41, N'123', N'368')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (42, N'123', N'358')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (43, N'113', N'128')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (44, N'113', N'129')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (45, N'113', N'207')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (46, N'123', N'363')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (47, N'113', N'177')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (48, N'113', N'172')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (49, N'113', N'140')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (50, N'113', N'504')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (51, N'13', N'95')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (52, N'123', N'361')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (53, N'13', N'62')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (54, N'113', N'167')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (55, N'113', N'157')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (56, N'123', N'361')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (57, N'123', N'358')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (58, N'123', N'361')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (59, N'63', N'16')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (60, N'113', N'167')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (61, N'13', N'91')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (62, N'113', N'172')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (63, N'13', N'93')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (64, N'113', N'202')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (65, N'123', N'365')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (66, N'113', N'157')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (67, N'113', N'202')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (68, N'123', N'356')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (69, N'123', N'354')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (70, N'123', N'358')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (71, N'13', N'61')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (72, N'123', N'342')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (73, N'113', N'144')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (74, N'13', N'86')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (75, N'113', N'100')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (76, N'123', N'347')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (77, N'113', N'136')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (78, N'13', N'62')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (79, N'13', N'62')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (80, N'63', N'16')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (81, N'113', N'137')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (82, N'113', N'156')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (83, N'63', N'16')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (84, N'123', N'357')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (85, N'113', N'505')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (86, N'123', N'342')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (87, N'63', N'7')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (88, N'113', N'131')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (89, N'13', N'94')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (90, N'113', N'203')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (91, N'113', N'100')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (92, N'113', N'500')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (93, N'123', N'342')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (94, N'123', N'358')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (95, N'13', N'60')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (96, N'113', N'505')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (97, N'123', N'347')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (98, N'123', N'359')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (99, N'113', N'137')
GO
INSERT [dbo].[simulateTable] ([UniqueId], [AKT], [PST]) VALUES (100, N'123', N'357')
GO

How T-SQL looks like AKT field become row and PST row become columns? Really need help

This topic was modified 4 years, 11 months ago by Adelia.

Jonathan AC Roberts

SSCoach

Points: 18347
More actions
December 12, 2019 at 7:15 pm
Answer
#3707504
Adelia wrote:
Thom A wrote:
I don't understand what you're asking here. PST is a column already.
Sorry. PST row become column
Do you mean you want to have all the PST values for each ATK on one row?
If so you just need a STUFF FOR XML PATH statement
```
SELECT a.AKT, 
       STUFF((SELECT DISTINCT ',' + PST FROM [dbo].[simulateTable] b WHERE a.AKT=b.AKT FOR XML PATH ('')), 1, 1, '') PSTs
  FROM [dbo].[simulateTable] a
 GROUP BY a.AKT
```

Viewing 5 posts - 1 through 4 (of 4 total)

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

Thom A SSC Guru Points: 99129 More actions · Answer 1

I don't understand what you're asking here. PST is a column already.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Adelia SSC-Addicted Points: 422 More actions · Answer 2

Thom A wrote:

I don't understand what you're asking here. PST is a column already.

Sorry. PST row become column

Adelia SSC-Addicted Points: 422 More actions · Answer 3

Both of you is correct. I'm wrong giving an output of explanation