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.
July 13, 2016 at 6:37 am
Eirikur, is your test database really called 'TEEST'? 🙂
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?
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.
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply