March 2, 2012 at 12:01 pm
I've been beating my head against the wall trying to figure this out:crazy:.
Select tablename, col1, col2, col3, etc. on just 1 row and dynamically. In this case that means going through a set of tables and selecting the tablename and column names for 1 row each. sql2008r2. Tried a pivot, creating the select stmt dynamically, cursors, while loops. Can't seem to find a solution, any help would be appreciated.
There is an exception to every rule, except this one...
March 2, 2012 at 12:06 pm
SQLHeap (3/2/2012)
I've been beating my head against the wall trying to figure this out:crazy:.Select tablename, col1, col2, col3, etc. on just 1 row and dynamically. In this case that means going through a set of tables and selecting the tablename and column names for 1 row each. sql2008r2. Tried a pivot, creating the select stmt dynamically, cursors, while loops. Can't seem to find a solution, any help would be appreciated.
Not enuf information to work on! I see that you have 308 points, which means you are on SSC.com for good amount time.. Please follow the netiquettes on questions. Read more here: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 2, 2012 at 1:19 pm
ok. lets create 2 tables:
/****** Object: Table [dbo].[table0001] Script Date: 03/02/2012 15:13:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table0001]') AND type in (N'U'))
DROP TABLE [dbo].[table0001]
GO
/****** Object: Table [dbo].[table0002] Script Date: 03/02/2012 15:13:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table0002]') AND type in (N'U'))
DROP TABLE [dbo].[table0002]
GO
/****** Object: Table [dbo].[table0001] Script Date: 03/02/2012 15:13:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table0001](
[col0001] [int] IDENTITY(1,1) NOT NULL,
[col0002] [int] NOT NULL,
[col0003] [int] NOT NULL,
[col0004] [int] NOT NULL,
[col0005] [int] NOT NULL,
[col0006] [decimal](19, 5) NULL,
[col0007] [nchar](50) NULL,
[col0008] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[col0001] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [TestDB]
) ON [TestDB]
GO
USE [TestDB]
GO
/****** Object: Table [dbo].[table0002] Script Date: 03/02/2012 15:13:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table0002](
[col0001] [int] IDENTITY(1,1) NOT NULL,
[col0002] [int] NOT NULL,
[col0003] [int] NOT NULL,
[col0004] [int] NOT NULL,
[col0005] [int] NOT NULL,
[col0006] [int] NOT NULL,
[col0007] [int] NOT NULL,
[col0008] [int] NOT NULL,
[col0009] [nvarchar](250) NULL,
PRIMARY KEY CLUSTERED
(
[col0001] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [TestDB]
) ON [TestDB]
GO
I'm trying to select the table name and column names on 1 row each so the results would look something like:
Tables | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9
--------------------------------------------------------------------
Table1 | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | NULL
Table2 | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9
There is an exception to every rule, except this one...
March 2, 2012 at 1:45 pm
I'm not saying that this will perform great, but something like this should work. You will have to expand the query to include the greatest number of columns that you can have.
SELECT object_id, OBJECT_NAME( object_id ), [1], [2], [3], [4], [5], [6]
FROM (
select object_id, Name, column_id
from sys.columns
) AS A
PIVOT(
MAX( Name )
FOR column_id in ( [1], [2], [3], [4], [5], [6] )
) AS P
March 2, 2012 at 1:58 pm
Thanks Recurs1on!
There is an exception to every rule, except this one...
March 2, 2012 at 2:00 pm
Look at taht.. Reply in 25 mins.. thats what provide sample data and tables will do!
March 2, 2012 at 2:13 pm
You could also do somthing simliar with a tally table and a GROUP BY. I'm not sure which one will perform better, or if you even need it to.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply