query format
-
CREATE TABLE [dbo].[Title]( [ID] [int] NULL,
[Description] [varchar](50) NULL
) ON [PRIMARY]INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(1,'Architect')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(2,'Developers')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(3,'Programmer')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(4,'Analyst')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(5,'Project Manager')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(6,'Business Analyst')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(7,'Director')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(8,'Application Manager')
How do you select the data to appear in 3 columns like this?
-
Could you explain your logic here? Why are Developers linked to the Maintenance and Director roles, why is Admin linked to Food Servies (Should this be Services?) and Manager? Why do Analysts and Project Manager's have no linked roles. Your data shows no details on how you would affiliate these roles with one another, so I can't see a distinct why to achieve what you are after.
Also, why is Chef excluded from your dataset, and where did Food "Servies" come from?
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
And what logic is supposed to change "Chef" to "Food Services"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This is just dummy data to show how I need the data displayed in 3 columns.
- MinhL7 - Sunday, October 8, 2017 3:19 PM
Then you should have no problems with a "dummy" solution.
WITH
cteSort AS
(
SELECT N = ROW_NUMBER() OVER (ORDER BY Description) -1
,[Description]
FROM dbo.Title
)
,
cteColumize AS
(
SELECT RowNum = N%3
,ColNum = N/3
,[Description]
FROM cteSort
)
SELECT Col0 = MAX(CASE WHEN ColNum = 0 THEN [Description] ELSE '' END)
,Col1 = MAX(CASE WHEN ColNum = 1 THEN [Description] ELSE '' END)
,Col2 = MAX(CASE WHEN ColNum = 2 THEN [Description] ELSE '' END)
FROM cteColumize
GROUP BY RowNum
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Sunday, October 8, 2017 11:45 PM
How do you make it look like the columns in the 1st post? Do I need a 3rd column to identify the kind of test data to group them in the 3 columns?
- MinhL7 - Wednesday, October 11, 2017 6:00 AM
Like the others said, you need to explain why the test data you provided ended up in the columns they did. We can only guess. And, yes, that could be in the form of a 3rd column.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
They're grouped
CREATE TABLE [dbo].[Title]( [ID] [int] NULL,
[Description] [varchar](50) NULL,
[Dept] [varchar](50) NULL
) ON [PRIMARY]INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(1,'Architect','IT')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(2,'Developers','IT')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(3,'Programmer','IT')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(4,'Analyst','IT')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(5,'Project Manager','Management')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(6,'Business Analyst','Management')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(7,'Director','Business')INSERT INTO [dbo].[Title]([ID],[Description])
VALUES(8,'Application Manager','Business') - MinhL7 - Wednesday, October 11, 2017 6:48 PM
When you post something that's supposed to work, please check it before you post it because it didn't work..
Msg 110, Level 15, State 1, Line 6There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
The following cleaned up code creates and populates the modified test table and produces the output as you first posted.
--=================================================================================================
-- Create the test environment. This is NOT a part of the solution.
--=================================================================================================
--===== If the old test table exists, drop it.
-- Commented out to protect the OP's original table if named the same as the test table.
/*
IF OBJECT_ID('dbo.Title','U') IS NOT NULL
DROP TABLE Title
*/
GO
--===== Create the new test table.
CREATE TABLE dbo.Title
(
ID INT NOT NULL PRIMARY KEY CLUSTERED
,[Description] VARCHAR(50) NOT NULL
,Dept VARCHAR(50) NOT NULL
)
;
--===== Populate the table with test data.
INSERT INTO dbo.Title
(ID,[Description],Dept)
VALUES (1,'Architect' ,'IT')
,(2,'Developers' ,'IT')
,(3,'Programmer' ,'IT')
,(4,'Analyst' ,'IT')
,(5,'Project Manager' ,'Management')
,(6,'Business Analyst' ,'Management')
,(7,'Director' ,'Business')
,(8,'Application Manager' ,'Business')
;
--=================================================================================================
-- Create the test environment. This is NOT a part of the solution.
--=================================================================================================
--===== Solve the problem
WITH
cteSort AS
(--==== Create artificial row numbers by Dept ordered by ID
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY ID) -1
,[Description]
,Dept
FROM dbo.Title
)--==== Create the CROSSTAB bases on the artificial row numbers.
SELECT 'IT' = MAX(CASE WHEN Dept = 'IT' THEN [Description] ELSE '' END)
,'Management' = MAX(CASE WHEN Dept = 'Management' THEN [Description] ELSE '' END)
,'Business' = MAX(CASE WHEN Dept = 'Business' THEN [Description] ELSE '' END)
FROM cteSort
GROUP BY RowNum
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Now to ask the hard question. Do you understand the code well enough to do it again for something else on your own?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, October 11, 2017 7:56 PM
Yes, thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply