July 13, 2016 at 6:00 am
I have 2 tables where i can search on PK. However 1 table has other columns than the other. Is there a way I can use the same query (except for changing tablename) with having the columns converted to rows?
CREATE TABLE [dbo].[Test](
[PK] [int] IDENTITY(1,1) NOT NULL,
[COLUMN1] [nvarchar](50) NOT NULL,
[COLUMN2] [nchar](10) NULL,
[COLUMN3] [nchar](10) NULL,
[COLUMN4] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[PK] 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 INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r1'
,'Valuein2r1'
,'VAluein3r1'
,'Valuein4r1')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r2'
,'Valuein2r2'
,'VAluein3r2'
,'Valuein4r2')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r3'
,'Valuein2r3'
,'VAluein3r3'
,'Valuein4r3')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r4'
,'Valuein2r4'
,'VAluein3r4'
,'Valuein4r4')
GO
select *
from test
where PK = 1
This will get me:
1Value in 1Value in 2VAlue in 3Value in 4
However, what i would like as result is:
select 1, 'Value in 1'
union
select 1, 'Value in 2'
union
select 1, 'Value in 3'
union
select 1, 'Value in 4'
Is there a way to achieve this, and when having a 'test2' i only need to change the tablename in the select?
July 13, 2016 at 6:11 am
Quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
CREATE TABLE [dbo].[Test](
[PK] [int] IDENTITY(1,1) NOT NULL,
[COLUMN1] [nvarchar](50) NOT NULL,
[COLUMN2] [nchar](10) NULL,
[COLUMN3] [nchar](10) NULL,
[COLUMN4] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[PK] 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 INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r1'
,'Valuein2r1'
,'VAluein3r1'
,'Valuein4r1')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r2'
,'Valuein2r2'
,'VAluein3r2'
,'Valuein4r2')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r3'
,'Valuein2r3'
,'VAluein3r3'
,'Valuein4r3')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r4'
,'Valuein2r4'
,'VAluein3r4'
,'Valuein4r4')
GO
SELECT
TT.PK
,X.COL
FROMdbo.TestTT
CROSS APPLY
(
SELECT TT.COLUMN1 UNION ALL
SELECT TT.COLUMN2 UNION ALL
SELECT TT.COLUMN3 UNION ALL
SELECT TT.COLUMN4
) AS X(COL);
Output
PK COL
--- -----------
1 Valuein1r1
1 Valuein2r1
1 VAluein3r1
1 Valuein4r1
2 Valuein1r2
2 Valuein2r2
2 VAluein3r2
2 Valuein4r2
3 Valuein1r3
3 Valuein2r3
3 VAluein3r3
3 Valuein4r3
4 Valuein1r4
4 Valuein2r4
4 VAluein3r4
4 Valuein4r4
July 13, 2016 at 6:19 am
Good for this table, however i cannot change the tablename without changing the 'cross apply' part, so for my other table, i need to change the whole query, in stead of only the table name.
July 13, 2016 at 6:36 am
peter 67432 (7/13/2016)
Good for this table, however i cannot change the tablename without changing the 'cross apply' part, so for my other table, i need to change the whole query, in stead of only the table name.
Why not? The table name is not referenced in the CROSS APPLY.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 13, 2016 at 6:37 am
Eirikur, is your test database really called 'TEEST'? 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 13, 2016 at 6:38 am
Sorry, you are right. I ment changing the column names :unsure:
July 13, 2016 at 6:47 am
peter 67432 (7/13/2016)
Sorry, you are right. I ment changing the column names :unsure:
So you want something which handles different column names dynamically?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 13, 2016 at 6:51 am
That's correct.
July 13, 2016 at 7:08 am
Like this?
DECLARE @SQL nvarchar(MAX) = N'',
@Table nvarchar(128) = N'Test'
SELECT @SQL = N'SELECT
TT.PK
,X.COL
FROMdbo.' + QUOTENAME(@Table) + N'TT
CROSS APPLY
(
' + STUFF(( SELECT N' UNION ALL
SELECT TT.' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@Table, 'U')
AND is_identity = 0
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 13, '')
+ N'
) AS X(COL);'
EXEC sp_executesql @SQL;
July 13, 2016 at 8:37 am
Phil Parkin (7/13/2016)
Eirikur, is your test database really called 'TEEST'? 🙂
Yees
😎
July 13, 2016 at 8:46 am
Exacty! thanks!
July 13, 2016 at 8:57 am
Nice, Luis.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 13, 2016 at 9:14 am
peter 67432 (7/13/2016)
Exacty! thanks!
Do you understand how the code works? Please, post questions you might have or you might not be able to support it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply